 | SQL Server 2008查询表结构信息，主要目的是方便查询字段说明
在网上查了几篇这方面的文章，发现Sql语句是针对SQL Server 2008以前的版本，不能用，而且代码比较乱，只好自己写了，写好了不能独享，希望能对看官有帮助
打开SQL Server Management Studio，选择好需要查看数据库表说明的库，打开查询分析器窗口，copy下面的代码，替换TableName为你想要查看的表名，执行即可。
---------------------------------------------------------------------
DECLARE @tableName VARCHAR(100)--表名
SET @tableName = 'MasterBill'
SELECT ISNULL((SELECT ep.value
FROM sys.sysobjects obj
INNER JOIN sys.extended_properties ep ON obj.id = ep.major_id
WHERE obj.name = @tableName
AND ep.minor_id = 0
), @tableName) AS tableDesc,
col.name AS colName,
CASE WHEN EXISTS ( SELECT 1
FROM sysobjects
WHERE xtype = 'PK'
AND name IN (SELECT name
FROM sysindexes
WHERE id = col.id
AND indid IN (SELECT
indid
FROM
sysindexkeys
WHERE
id = col.id
AND colid = col.colid
)
) ) THEN 'Y'
ELSE ''
END AS isPK,
t.name AS dataType,
col.length AS [dataLength],
CASE col.isnullable
WHEN 1 THEN 'Y'
ELSE 'N'
END AS isNullable,
ISNULL(colDefault.text, '') AS defaultVal,
ISNULL(ep.value, '') AS ColDesc
FROM sys.syscolumns col
INNER JOIN sys.sysobjects obj ON obj.id = col.id
INNER JOIN systypes t ON col.xtype = t.xusertype
LEFT JOIN syscomments colDefault ON col.cdefault = colDefault.id
LEFT JOIN sys.extended_properties ep ON ep.major_id = obj.id
AND col.colorder = ep.minor_id
AND ep.minor_id > 0
WHERE obj.name = @tableName
ORDER BY col.colorder
| |
