本文共 1706 字，大约阅读时间需要 5 分钟。

DECLARE @TableName NVARCHAR(50) SET @TableName = 'Sys_Bank'  select   A.name   as   外键关联的表,B.name   as   外键所在的表,C.name   as   外键名称  from   sysforeignkeys    inner   join   sysobjects   A   on   A.id=sysforeignkeys.fkeyid    inner   join   sysobjects   B   on   B.id=sysforeignkeys.rkeyid  inner   join   sysobjects   C   on   C.id=sysforeignkeys.constid where a.name = @TableName SELECT      --表名       = case when a.colorder=1 then d.name else '' end,     --表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end, -- NO 字段名 主键 自增 类型 长度 空值 默认值 外键 PK表名 PK字段 说明     NO   = a.colorder,     字段名     = a.name,     主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (                      SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then 'Y' else '' end, 自增       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then 'Y'else '' end,     类型       = b.name,     长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'), --占用字节数 = a.length,     --小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),     空值     = case when a.isnullable=1 then 'Y'else 'N' end,     默认值     = isnull(e.text,''), 外键 = '', PK表名 = '', PK字段 = '',     说明   = isnull(g.[value],a.name) FROM syscolumns a  left join systypes b on a.xusertype=b.xusertype inner join sysobjects d on a.id=d.id  and d.xtype='U' and  d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id   left join sys.extended_properties f on d.id=f.major_id and f.minor_id = 0 --left join sysforeignkeys k on a.id = k.fkeyid  --inner   join   sysobjects   KA   on   KA.id=k.fkeyid    --inner   join   sysobjects   KB   on   KB.id=k.rkeyid  --inner   join   sysobjects   KC   on   KC.id=k.constid where d.name=@TableName    --如果只查询指定表,加上此条件 order by a.id,a.colorder

转载地址：http://uvzmi.baihongyu.com/