- sql 2000
TitleSELECT D.Name as TableName, A.colorder AS ColOrder, A.name AS Name,
COLUMNPROPERTY(A.ID,A.Name, 'IsIdentity') AS IsIdentity,
CASE WHEN EXISTS
(SELECT 1
FROM dbo.sysobjects
WHERE Xtype = 'PK' AND Name IN
(SELECT Name
FROM sysindexes
WHERE indid IN
(SELECT indid
FROM sysindexkeys
WHERE ID = A.ID AND colid = A.colid)))
THEN 1 ELSE 0 END AS 主键,
B.name AS 类型,
A.length AS [长度],
A.xprec AS [精度],
A.xscale AS [小数],
CASE WHEN A.isnullable = 1 THEN 1 ELSE 0 END AS [可为空],
ISNULL(E.text, ' ') AS [默认值],
ISNULL(G.[value], ' ') AS [说明]
FROM dbo.syscolumns A LEFT OUTER 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.name <> 'dtproperties' LEFT OUTER JOIN
dbo.syscomments E ON A.cdefault = E.id LEFT OUTER JOIN
dbo.sysproperties G ON A.id = G.id AND A.colid = G.smallid
--WHERE D.Name='tablename' --如果找指定表,把注释去掉
ORDER BY 1, 2 - sql 2005
SELECT D.Name as TableName, A.colorder AS ColOrder, A.name AS Name,
COLUMNPROPERTY(A.ID,A.Name, 'IsIdentity') AS IsIdentity,
CASE WHEN EXISTS
(SELECT 1
FROM dbo.sysobjects
WHERE Xtype = 'PK' AND Name IN
(SELECT Name
FROM sysindexes
WHERE indid IN
(SELECT indid
FROM sysindexkeys
WHERE ID = A.ID AND colid = A.colid)))
THEN 1 ELSE 0 END AS 主键,
B.name AS 类型,
A.length AS [长度],
A.xprec AS [精度],
A.xscale AS [小数],
CASE WHEN A.isnullable = 1 THEN 1 ELSE 0 END AS [可为空],
ISNULL(E.text, ' ') AS [默认值],
ISNULL(G.[value], ' ') AS [说明]
FROM dbo.syscolumns A LEFT OUTER 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.name <> 'dtproperties' LEFT OUTER JOIN
dbo.syscomments E ON A.cdefault = E.id LEFT OUTER JOIN
sys.extended_properties G ON A.id = G.major_id AND A.colid = G.minor_id
WHERE D.Name='Suspect' --如果找指定表,把注释去掉
ORDER BY 1, 2 - 小工具
/Files/skyangell/sqldoc_src.rar
用心对待身边的一切,一切皆有可能!
发表于
2009-09-11 18:57
那时花开
阅读(418)
评论()
收藏
举报
管理