SQL Server 一句Sql把表結(jié)構(gòu)全部查詢出來

?--一句Sql把表結(jié)構(gòu)全部查詢出來

SELECT
表名?=?Case?When?A.colorder=1?Then?D.name?Else?''?End,
表說明?=?Case?When?A.colorder=1?Then?isnull(F.value,'')?Else?''?End,
字段序號(hào)?=?A.colorder,
字段名?=?A.name,
字段說明?=?isnull(G.[value],''),
標(biāo)識(shí)?=?Case?When?COLUMNPROPERTY(?A.id,A.name,'IsIdentity')=1?Then?'√'Else?''?End,
主鍵?=?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?'√'?else?''?end,
類型?=?B.name,
--占用字節(jié)數(shù)?=?A.Length,
長度?=?COLUMNPROPERTY(A.id,A.name,'PRECISION'),
小數(shù)位數(shù)?=?isnull(COLUMNPROPERTY(A.id,A.name,'Scale'),0),
允許空?=?Case?When?A.isnullable=1?Then?'√'Else?''?End,
默認(rèn)值?=?isnull(E.Text,'')
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
--where?d.name='OrderInfo'?--如果只查詢指定表,加上此條件
Order?By
A.id,A.colorder