select
tabname=o.name,
columnline=c.column_id,
columnname=c.name,
typenum=t.name,
typelength=c.max_length,
fstate=isnull(g.value,n''),
isablenull=case when c.is_nullable=1 then n'√'else n'' end,
defaultdata=isnull(d.definition,n''),
isidentity=case when c.is_identity=1 then n'√'else n'' end,
isprimary=case when exists(select 1 from sysobjects where xtype='pk' and parent_obj=c.[object_id] and name in (
select name from sysindexes where indid in( select indid from sysindexkeys where id = c.[object_id] and colid=c.column_id))) then '√' else '' end,
isforeign=case when exists(select * from sysforeignkeys fk where c.[object_id]=fk.fkeyid and c.column_id=fk.fkey)then '√' else '' end,
tabforeignname=isnull(idx.fkname,n''),
outnamecol=isnull(idx.ns,n'')
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.extended_properties g
on c.[object_id]=g.major_id and c.column_id=g.minor_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 sysforeignkeys fk
on c.[object_id]=fk.fkeyid
and c.column_id=fk.fkey
left join -- 索引及主键信息
(
select
idx.fkeyid,
idx.fkey,
fkname=o.name,
ns=ss.name
from sysforeignkeys idx
inner join sys.objects o
on idx.rkeyid=o.[object_id]
and o.type='u'
and o.is_ms_shipped=0
left join syscolumns ss
on idx.rkeyid=ss.id
and idx.rkey=ss.colid
)idx
on c.[object_id]=idx.fkeyid
and c.column_id=idx.fkey
where o.name=n'{0}' ------要查询的表名
order by o.name,c.column_id
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!!
点击进行留言回复
网友评论