三个实用的数据库字典物业

苏武的人物形象不应太悲 【赛迪－IT技术报道】本文主要介绍了三个实用的SQL Server数据库字典SQL语句，数据库字典主要包括表结构(分为SQL Server 2000和SQL Server 2005)、索引和主键. 外键.约束.视图.函数.存储过程.触发器。你可以在查询分析器、企业管理器中简单执行后，快速的查出SQL Server 2000及SQL Server 2005的全部数据字典。

1. SQL Server 2000数据库字典（表结构.sql）

SELECT TOP 100 PERCENT ,

CASE WHEN lorder = 1 THEN ELSE \"\" END AS 表名,

CASE WHEN lorder = 1 THEN isnull(lue, \"\") ELSE \"\" END AS 表说明,

lorder AS 字段序号, AS 字段名, CASE WHEN COLUMNPROPERTY(,

, \"IsIdentity\") = 1 THEN \"√\" ELSE \"\" END AS 标识,

CASE WHEN EXISTS

(SELECT 1

FROM sindexes si INNER JOIN

sindexkeys sik ON = AND did = did INNER JOIN

scolumns sc ON = AND lid = lid INNER JOIN

sobjects so ON = AND ype = \"PK\"

WHERE = AND lid = lid) THEN \"√\" ELSE \"\" END AS 主键,

AS 类型, ngth AS 长度, COLUMNPROPERTY(, , \"PRECISION\")

AS 精度, ISNULL(COLUMNPROPERTY(, , \"Scale\"), 0) AS 小数位数,

CASE WHEN nullable = 1 THEN \"√\" ELSE \"\" END AS 允许空, ISNULL(xt, \"\")

AS 默认值, ISNULL(g.[value], \"\") AS 字段说明, date AS 创建时间,

CASE WHEN lorder = 1 THEN fdate ELSE NULL END AS 更改时间

FROM scolumns a LEFT OUTER JOIN

stypes b ON ype = sertype INNER JOIN

sobjects d ON = AND ype = \"U\" AND

d.status >= 0 LEFT OUTER JOIN

scomments e ON efault = LEFT OUTER JOIN

sproperties g ON = AND lid = allid AND

= \"MS_Description\" LEFT OUTER JOIN

sproperties f ON = AND allid = 0 AND

= \"MS_Description\"

ORDER BY , lorder

◆SQL Server 2005数据库字典（表结构.sql）

SELECT TOP 100 PERCENT ,

CASE WHEN lorder = 1 THEN ELSE \"\" END AS 表名,

CASE WHEN lorder = 1 THEN isnull(lue, \"\") ELSE \"\" END AS 表说明,

lorder AS 字段序号, AS 字段名, CASE WHEN COLUMNPROPERTY(,

, \"IsIdentity\") = 1 THEN \"√\" ELSE \"\" END AS 标识,

CASE WHEN EXISTS

(SELECT 1

FROM sindexes si INNER JOIN

sindexkeys sik ON = AND did = did INNER JOIN

scolumns sc ON = AND lid = lid INNER JOIN

sobjects so ON = AND ype = \"PK\"

WHERE = AND lid = lid) THEN \"√\" ELSE \"\" END AS 主键,

AS 类型, ngth AS 长度, COLUMNPROPERTY(, , \"PRECISION\")

AS 精度, ISNULL(COLUMNPROPERTY(, , \"Scale\"), 0) AS 小数位数,

CASE WHEN nullable = 1 THEN \"√\" ELSE \"\" END AS 允许空, ISNULL(xt, \"\")

AS 默认值, ISNULL(g.[value], \"\") AS 字段说明, date AS 创建时间,

CASE WHEN lorder = 1 THEN fdate ELSE NULL END AS 更改时间

FROM scolumns a LEFT OUTER JOIN

stypes b ON ype = sertype INNER JOIN

sobjects d ON = AND ype = \"U\" AND

d.status >= 0 LEFT OUTER JOIN

scomments e ON efault = LEFT OUTER JOIN

sproperties g ON = AND lid = allid AND

= \"MS_Description\" LEFT OUTER JOIN

sproperties f ON = AND allid = 0 AND

= \"MS_Description\"

ORDER BY , lorder

L Server数据库字典（索引.sql）

SELECT TOP 100 PERCENT ,

CASE WHEN yno = 1 THEN ELSE \"\" END AS 表名,

CASE WHEN yno = 1 THEN ELSE \"\" END AS 索引名称, AS 列名,

yno AS 索引顺序, CASE indexkey_property(, did, yno, \"isdescending\")

WHEN 1 THEN \"降序\" WHEN 0 THEN \"升序\" END AS 排序, CASE WHEN IS NULL

THEN \"\" ELSE \"√\" END AS 主键, CASE INDEXPROPERTY(, , \"IsClustered\")

WHEN 1 THEN \"√\" WHEN 0 THEN \"\" END AS 聚集, CASE INDEXPROPERTY(,

, \"IsUnique\") WHEN 1 THEN \"√\" WHEN 0 THEN \"\" END AS 唯一,

CASE WHEN IS NULL THEN \"\" ELSE \"√\" END AS 唯一约束,

igFillFactor AS 填充因子, date AS 创建时间, fdate AS 更改时间

FROM sindexes a INNER JOIN

sindexkeys b ON = AND did = did INNER JOIN

scolumns d ON = AND lid = lid INNER JOIN

sobjects c ON = AND ype = \"U\" LEFT OUTER JOIN

sobjects e ON = AND ype = \"UQ\" LEFT OUTER JOIN

sobjects p ON = AND ype = \"PK\"

WHERE (OBJECTPROPERTY(, N\"IsUserTable\") = 1) AND (OBJECTPROPERTY(,

N\"IsMSShipped\") = 0) AND (INDEXPROPERTY(, , \"IsAutoStatistics\") = 0)

ORDER BY , , yno

L Server数据库字典（主键.外键.约束.视图.函数.存储过程.触发器.sql）

SELECT DISTINCT

TOP 100 PERCENT ype,

CASE ype WHEN \"X\" THEN \"扩展存储过程\" WHEN \"TR\" THEN \"触发器\" WHEN \"PK\" THEN

\"主键\" WHEN \"F\" THEN \"外键\" WHEN \"C\" THEN \"约束\" WHEN \"V\" THEN \"视图\" WHEN \"FN\"

THEN \"函数-标量\" WHEN \"IF\" THEN \"函数-内嵌\" WHEN \"TF\" THEN \"函数-表值\" ELSE \"存储过程\"

END AS 类型, AS 对象名, date AS 创建时间, fdate AS 更改时间,

xt AS 声明语句

FROM sobjects o LEFT OUTER JOIN

scomments c ON =

WHERE (ype IN (\"X\", \"TR\", \"C\", \"V\", \"F\", \"IF\", \"TF\", \"FN\", \"P\", \"PK\")) AND

(OBJECTPROPERTY(, N\"IsMSShipped\") = 0)

ORDER BY CASE ype WHEN \"X\" THEN \"扩展存储过程\" WHEN \"TR\" THEN \"触发器\" WHEN

\"PK\" THEN \"主键\" WHEN \"F\" THEN \"外键\" WHEN \"C\" THEN \"约束\" WHEN \"V\" THEN \"视图\"

WHEN \"FN\" THEN \"函数-标量\" WHEN \"IF\" THEN \"函数-内嵌\" WHEN \"TF\" THEN \"函数-表值\"

ELSE \"存储过程\" END DESC

贵阳子宫内膜炎
类风湿关节炎疼痛如何缓解
合肥华夏白癜风医院