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 ,
??????? 標(biāo)識 = 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') ,
??????? 小數(shù)位數(shù) = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) ,
??????? 允許空 = CASE WHEN a.isnullable = 1 THEN '√'
?????????????????? ELSE ''
????????????? END ,
??????? 默認(rèn)值 = ISNULL(e.text, '') ,
??????? 字段說明 = ISNULL(g.[value], '') ,
??????? 創(chuàng)建時間 = 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
?????????????????????????????????????????????? AND g.class_desc = 'OBJECT_OR_COLUMN'
??????? 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 = 'Address'? --這里輸入包含表名稱的條件
ORDER BY d.id ,
??????? a.colorder

SELECT
?--OBJECT_ID(a.TABLE_SCHEMA + '.' + a.TABLE_NAME) AS [object_id] ,
??????? CASE WHEN a.ORDINAL_POSITION = 1
???????????? THEN a.TABLE_SCHEMA + '.' + a.TABLE_NAME
???????????? ELSE ''
??????? END AS TABLE_NAME ,
??????? CASE WHEN ( a.ORDINAL_POSITION = 1
??????????????????? AND p1.value IS NOT NULL
????????????????? ) THEN p1.value
???????????? ELSE ''
??????? END AS TABLE_Description ,
??????? a.COLUMN_NAME ,
??????? CASE WHEN ( ( CHARINDEX('char', a.DATA_TYPE) > 0
????????????????????? OR CHARINDEX('binary', a.DATA_TYPE) > 0
??????????????????? )
??????????????????? AND a.CHARACTER_MAXIMUM_LENGTH <> -1
????????????????? )
???????????? THEN a.DATA_TYPE + '('
????????????????? + CAST(a.CHARACTER_MAXIMUM_LENGTH AS VARCHAR(4)) + ')'
???????????? WHEN ( ( CHARINDEX('CHAR', a.DATA_TYPE) > 0
????????????????????? OR CHARINDEX('binary', a.DATA_TYPE) > 0
??????????????????? )
??????????????????? AND a.CHARACTER_MAXIMUM_LENGTH = -1
????????????????? ) THEN a.DATA_TYPE + '(max)'
???????????? WHEN ( CHARINDEX('numeric', a.DATA_TYPE) > 0 )
???????????? THEN a.DATA_TYPE + '(' + CAST(a.NUMERIC_PRECISION AS VARCHAR(4))
????????????????? + ',' + CAST(a.NUMERIC_SCALE AS VARCHAR(4)) + ')'
???????????? ELSE a.DATA_TYPE
??????? END AS COLUMN_TYPE ,
??????? CASE WHEN c.IS_IDENTITY = 1 THEN 'YES'
???????????? ELSE 'NO'
??????? END AS IS_IDENTITY ,
??????? a.IS_NULLABLE ,
??????? CASE WHEN a.COLUMN_DEFAULT IS NULL THEN ''
???????????? ELSE a.COLUMN_DEFAULT
??????? END AS Default_Value ,
??????? CASE WHEN p.value IS NULL THEN ''
???????????? ELSE p.value
??????? END AS [COLUMN_Description] ,
??????? CASE WHEN o.name IS? NULL THEN ''
???????????? ELSE '√'
??????? END AS Is_PrimaryKey ,
??????? CASE WHEN f.parent_column_id IS NULL THEN ''
???????????? ELSE '√'
??????? END AS Is_Foreignkeys ,
??????? CASE WHEN referenced_object_id IS NULL THEN ''
???????????? ELSE OBJECT_NAME(referenced_object_id)
??????? END AS Foreign_Table ,
??????? CASE WHEN referenced_object_id IS NULL THEN ''
???????????? ELSE ( SELECT? name
??????????????????? FROM??? sys.columns
??????????????????? WHERE?? object_id = f.referenced_object_id
??????????????????????????? AND column_id = f.referenced_column_id
????????????????? )
??????? END AS Foreign_key
FROM??? INFORMATION_SCHEMA.COLUMNS a
??????? INNER JOIN sys.columns c ON OBJECT_ID(a.TABLE_SCHEMA + '.'
????????????????????????????????????????????? + a.TABLE_NAME) = c.OBJECT_ID
??????????????????????????????????? AND a.COLUMN_NAME = c.NAME
??????? LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
?????????????????????????????????????????????????????????? AND a.TABLE_NAME = b.TABLE_NAME
?????????????????????????????????????????????????????????? AND a.COLUMN_NAME = b.COLUMN_NAME
??????? LEFT JOIN sys.sysobjects o ON o.name = b.CONSTRAINT_NAME
????????????????????????????????????? AND o.xtype = 'PK'
??????? LEFT JOIN sys.extended_properties p ON OBJECT_ID(a.TABLE_SCHEMA + '.'
???????????????????????????????????????????????????????? + a.TABLE_NAME) = p.major_id
?????????????????????????????????????????????? AND a.Ordinal_position = p.minor_id
?????????????????????????????????????????????? AND p.class_desc = 'OBJECT_OR_COLUMN'
??????? LEFT JOIN sys.extended_properties p1 ON OBJECT_ID(a.TABLE_SCHEMA + '.'
????????????????????????????????????????????????????????? + a.TABLE_NAME) = p1.major_id
??????????????????????????????????????????????? AND p1.minor_id = 0
??????? LEFT JOIN SYS.foreign_key_columns f ON OBJECT_ID(a.TABLE_SCHEMA + '.'
???????????????????????????????????????????????????????? + a.TABLE_NAME) = f.parent_object_id
?????????????????????????????????????????????? AND a.ORDINAL_POSITION = f.parent_column_id
WHERE?? a.TABLE_NAME = 'Address'
-- a.TABLE_NAME IN (SELECT name FROM sys.tables)
ORDER BY a.TABLE_SCHEMA,a.TABLE_NAME, a.ORDINAL_POSITION