TinfoAll 查询所以表关系

最新推荐文章于 2025-01-04 23:45:08 发布

晚上吃酱牛肉

最新推荐文章于 2025-01-04 23:45:08 发布

阅读量502

点赞数

分类专栏： SQL 文章标签： object constraints join properties date null

SQL 专栏收录该内容

7 篇文章

订阅专栏

本文介绍了一个用于SQL Server的自定义表值函数TinfoAll，该函数能够查询数据库中所有表及其列的详细信息，包括表名、字段描述、类型、默认值等，并支持显示外键关联。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

create FUNCTION [dbo].[TinfoAll] RETURNS TABLE AS
RETURN

select TOP 100 PERCENT * from (
select

         --   tabledesc,
     all_name,
     column_id = convert(varchar(100),column_id),
     columnname,
     ColumnDesc,
     type,
     primarykey,--=case when fk is not null then primarykey+'/'+isnull(fk,'') else primarykey end,
     nullable,
     [IDENTITY],
     [Default] = case when [Default] ='(getdate())' then replace([Default],'((','(') else replace(replace([Default],'((','('),'))',')') end,
     --fk,
     name

from (
   select aa.tablename,
     aa.tabledesc,
     aa.all_name,
     aa.column_id,
     aa.ColumnDesc,
     aa.columnname,
     aa.type,
     primarykey=case when bb.fk is not null then replace (aa.primarykey ,' ','')+ case when aa.primarykey =N'√' then '/' else '' end +isnull(bb.fk,'') else aa.primarykey end,
     aa.nullable,
     aa.[IDENTITY],
     aa.[Default],
     bb.fk,
     aa.name
     from (
       SELECT
          TableName=CASE WHEN C.column_id=1 THEN O.name ELSE N'' END,
          TableDesc=ISNULL(CASE WHEN C.column_id=1 THEN PTB.[value] END,N''),
          All_name=CASE WHEN C.column_id=1 THEN O.name ELSE null END+ case when ISNULL(CASE WHEN C.column_id=1 THEN convert(varchar(3000),PTB.[value]) END,N'') ='' then '' else '('+ISNULL(CASE WHEN C.column_id=1 THEN convert(varchar(3000),PTB.[value]) END,N'')+')' end ,
          Column_id=C.column_id,
          ColumnDesc=ISNULL(PFD.[value],N''),
          ColumnName=C.name,
          Type=case when t.name like '%char%' then T.name+'('+convert(varchar(100),C.max_length)+')' else t.name end,
          --Length=C.max_length,
          PrimaryKey=ISNULL(IDX.PrimaryKey,N''),
          NullAble=CASE WHEN C.is_nullable=1 THEN N'√'ELSE N'' END,
          [Default]=ISNULL(D.definition,N''),
          -- [IDENTITY]=CASE WHEN C.is_identity=1 THEN N'√'ELSE N'' END,
          [IDENTITY]=CASE
             WHEN C.is_identity=1
               THEN N'( '
               +RTRIM(IDENT_SEED(O.name))
               +N' , '
               +RTRIM(IDENT_INCR(O.name))
               +N' )'
             ELSE N'' END,
          Computed=CASE WHEN C.is_computed=1 THEN N'√'ELSE N'' END,

Precision=C.precision,
Scale=C.scale,

          IndexName=ISNULL(IDX.IndexName,N''),
          IndexSort=ISNULL(IDX.Sort,N''),
          Create_Date=O.Create_Date,
          Modify_Date=O.Modify_date,
          O.name,
          [object_id]=object_id(O.name)
       FROM sys.columns C
       INNER JOIN sys.objects O
        ON C.[object_id]=O.[object_id]
          AND O.type='U'
          AND O.is_ms_shipped=0
       INNER JOIN sys.types T
        ON C.user_type_id=T.user_type_id
       LEFT JOIN sys.default_constraints D
        ON C.[object_id]=D.parent_object_id
          AND C.column_id=D.parent_column_id
          AND C.default_object_id=D.[object_id]
       LEFT JOIN sys.extended_properties PFD
        ON PFD.class=1
          AND C.[object_id]=PFD.major_id
          AND C.column_id=PFD.minor_id
       -- AND PFD.name='Caption' -- 字段说明对应的描述名称(一个字段可以添加多个不同name的描述)
       LEFT JOIN sys.extended_properties PTB
        ON PTB.class=1
          AND PTB.minor_id=0
          AND C.[object_id]=PTB.major_id
       -- AND PFD.name='Caption' -- 表说明对应的描述名称(一个表可以添加多个不同name的描述)
       LEFT JOIN -- 索引及主键信息
       (
         SELECT
         IDXC.[object_id],
         IDXC.column_id,
         Sort=CASE INDEXKEY_PROPERTY(IDXC.[object_id],IDXC.index_id,IDXC.index_column_id,'IsDescending')
         WHEN 1 THEN 'DESC' WHEN 0 THEN 'ASC' ELSE '' END,
         PrimaryKey=CASE WHEN IDX.is_primary_key=1 THEN N'√'ELSE N'' END,
         IndexName=IDX.Name
         FROM sys.indexes IDX
         INNER JOIN sys.index_columns IDXC
         ON IDX.[object_id]=IDXC.[object_id]
         AND IDX.index_id=IDXC.index_id
         LEFT JOIN sys.key_constraints KC
         ON IDX.[object_id]=KC.[parent_object_id]
         AND IDX.index_id=KC.unique_index_id
         INNER JOIN -- 对于一个列包含多个索引的情况,只显示第1个索引信息
         (
         SELECT [object_id], Column_id, index_id=MIN(index_id)
         FROM sys.index_columns
         GROUP BY [object_id], Column_id
         ) IDXCUQ
         ON IDXC.[object_id]=IDXCUQ.[object_id]
         AND IDXC.Column_id=IDXCUQ.Column_id
         AND IDXC.index_id=IDXCUQ.index_id
       ) IDX
       ON C.[object_id]=IDX.[object_id]
       AND C.column_id=IDX.column_id
       -- WHERE O.name=N'要查询的表' -- 如果只查询指定表,加上此条件
       ) aa
   left join
       (
       select *,
       '(FK:'+object_name(rkeyid)+'.'+(select name from sys.columns where object_id=e.rkeyid and column_id =e.rkey)+')' fk
       from sys.sysforeignkeys e
       ) bb
   on aa.object_id =bb.fkeyid and aa.column_id =bb.fkey
)bb

union all

select null,b.*,a.name from sys.objects a,
(
select 'Column_id' as xuhao,
'Comumnname' e,
'ColumnDesc' c, 'Type' t,'PrimaryKey' p,'Nullable' n,'IDENTITY' i,'Default' df
) as b
where type = 'u'
) aabb
ORDER BY name,case when isnumeric( column_id ) =1 then convert(int,column_id) else 0 end