SQL2000下执行语句:
SELECT
sysobjects.name AS [表名],
sysproperties.[value] AS [表说明],
syscolumns.name AS [列名],
properties.[value] AS [字段说明],
systypes.name AS [字段类型],
syscolumns.length AS [长度],
ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name, "Scale"), 0) AS [小数位数],
CASE syscolumns.isnullable WHEN "1" THEN "Y" ELSE "N" END AS [是否允许空值],
CASE WHEN syscomments.text IS NULL THEN "" ELSE syscomments.text END AS [默认值],
CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, "IsIdentity") = 1 THEN "√" ELSE "" END AS [自增标识],
CASE WHEN EXISTS (SELECT 1 FROM sysobjects WHERE xtype = "PK" AND name IN
(SELECT name
FROM sysindexes
WHERE indid IN
(SELECT indid
FROM sysindexkeys
WHERE id = syscolumns.id AND colid = syscolumns.colid)))
THEN "√" ELSE "" END AS [主键]
FROM syscolumns INNER JOIN
sysobjects ON sysobjects.id = syscolumns.id INNER JOIN
systypes ON syscolumns.xtype = systypes.xtype LEFT OUTER JOIN
sysproperties properties ON syscolumns.id = properties.id AND
syscolumns.colid = properties.smallid LEFT OUTER JOIN
sysproperties ON sysobjects.id = sysproperties.id AND
sysproperties.smallid = 0 LEFT OUTER JOIN
syscomments ON syscolumns.cdefault = syscomments.id
WHERE (sysobjects.xtype = "U")
--AND sysobjects.NAME="表名" --只查询某个表的结构,否则将本行注释掉就是全库
ORDER BY [表名],[列名]
--SQL2005下执行语句:
SELECT
Sysobjects.name AS [表名],
syscolumns.name AS [列名],
systypes.name AS [数据类型],
syscolumns.length AS [字段长度],
sys.extended_properties.[value] AS [字段描述],
syscomments.text AS [默认值],
CASE syscolumns.isnullable WHEN "1" THEN "Y" ELSE "N" END AS [是否允许空值],
ISNULL(COLUMNPROPERTY(syscolumns.id, syscolumns.name, "Scale"), 0) AS [小数位数],
COLUMNPROPERTY(syscolumns.id, syscolumns.name, "IsIdentity") AS [自增标识] ,
CASE WHEN EXISTS (SELECT 1 FROM sysobjects WHERE xtype = "PK" AND name IN
(SELECT name
FROM sysindexes
WHERE indid IN
(SELECT indid
FROM sysindexkeys
WHERE id = syscolumns.id AND colid = syscolumns.colid)))
THEN "√" ELSE "" END AS [主键]
FROM syscolumns
INNER JOIN systypes
ON syscolumns.xtype = systypes.xtype
LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id
LEFT OUTER JOIN sys.extended_properties ON
( sys.extended_properties.minor_id = syscolumns.colid
AND sys.extended_properties.major_id = syscolumns.id)
LEFT OUTER JOIN syscomments ON syscolumns.cdefault = syscomments.id
WHERE (systypes.name <> "sysname")
-- AND syscolumns.id IN (SELECT id FROM SYSOBJECTS WHERE xtype = "U" AND NAME = "表名") --仅查某表,否则注释掉本行为全库
ORDER BY [表名],[列名]
计算机等级考试成绩什么时候出来_计算机等级考试辅导:SQL2k/2005查询表的结构信息
http://m.kwkids.com/jisuanjilei/8376.html
推荐访问:计算机等级考试时间 计算机等级考试官网