欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

基于SQL2005 SQL2008 表结构信息查询升级版的详解(含外键信息)

程序员文章站 2022-06-04 23:10:44
select 表名=case when a.colorder = 1 then d.name else '' end, 表说明=case when a.colorder =...
select
表名=case when a.colorder = 1 then d.name else '' end,
表说明=case when a.colorder = 1 then isnull(f.value, '') else '' end ,
字段序号=a.colorder ,
字段名=a.name ,
标识=case when columnproperty(a.id, a.name, 'isidentity') = 1 then '√' else '' end ,
主键=case when exists (
  select 1
  from dbo.sysindexes si
  inner join dbo.sysindexkeys sik on si.id = sik.id and si.indid = sik.indid
  inner join dbo.syscolumns sc on sc.id = sik.id and sc.colid = sik.colid
  inner join dbo.sysobjects so on so.name = so.name and so.xtype = 'pk'
  where sc.id = a.id and sc.colid = a.colid) then '√'
  else ''
 end ,
 外键=case when tony.fkey is not null and tony.fkey=a.colid then '√' else '' end ,
 外键表=case when tony.fkey is not null and tony.fkey=a.colid
then object_name(tony.fkeyid) else ''
   end ,
 外键字段=case when tony.fkey is not null and tony.fkey=a.colid
 then (select name from syscolumns
   where colid=tony.fkey and id=tony.fkeyid)  
 else ''
 end,
 类型=b.name ,
 长度=a.length ,
 精度=columnproperty(a.id, a.name, 'precision') ,
 小数位数=isnull(columnproperty(a.id, a.name, 'scale'), 0) ,
 允许空=case when a.isnullable = 1 then '√' else '' end ,
 默认值= isnull(e.text, ''),
 字段说明=isnull(g.[value], ''),
 创建时间=d.crdate,
 更改时间=case when a.colorder = 1 then d.refdate else null end
from dbo.syscolumns a
left join dbo.systypes b on a.xtype = b.xusertype
inner join dbo.sysobjects d on a.id = d.id and d.xtype = 'u' and d.status >= 0
left join dbo.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 sysobjects htl on htl.parent_obj=d.id and htl.xtype='f'
left join sysforeignkeys tony on htl.id=tony.constid
where d.name='tb'  --这里输入包含表名称的条件
order by d.id, a.colorder