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
