MySQL快捷创建S_SC_C视图_3个SQL视图搞定所有SqlServer数据库字典

网上有很多 数据库 专区 href&＃61;"http://dev.yesky.com/devsjk" target&＃61;_blank>SQL SERVER 数据库 字典 的SQL语句&＃xff0c;七零八落&＃xff0c;我在工作整理了一下思路&＃xff0c;总结SQL代码如下&＃xff0c;只在SQLSERVER2000中测试通过&＃xff0c;希望对大家有帮助。 1. SqlServer 数据库 字典 --表结构.

网上有很多数据库专区 href&＃61;"http://dev.yesky.com/devsjk" target&＃61;_blank>SQL SERVER数据库字典的SQL语句&＃xff0c;七零八落&＃xff0c;我在工作整理了一下思路&＃xff0c;总结SQL代码如下&＃xff0c;只在SQLSERVER2000中测试通过&＃xff0c;希望对大家有帮助。

1. SqlServer数据库字典--表结构.sql

以下是引用片段&＃xff1a;

SELECT TOP 100 PERCENT --a.id,

CASE WHEN a.colorder &＃61; 1 THEN d.name ELSE &＃39;&＃39; END AS 表名,

CASE WHEN a.colorder &＃61; 1 THEN isnull(f.value, &＃39;&＃39;) ELSE &＃39;&＃39; END AS 表说明,

a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id,

a.name, &＃39;IsIdentity&＃39;) &＃61; 1 THEN &＃39;√&＃39; ELSE &＃39;&＃39; END AS 标识,

CASE WHEN EXISTS

(SELECT 1

FROM dbo.sysindexes si INNER JOIN

dbo.sysindexkeys sik ON si.id &＃61; sik.id AND si.indid &＃61; sik.indid INNER JOIN

dbo.syscolumns sc ON sc.id &＃61; sik.id AND sc.colid &＃61; sik.colid INNER JOIN

dbo.sysobjects so ON so.name &＃61; so.name AND so.xtype &＃61; &＃39;PK&＃39;

WHERE sc.id &＃61; a.id AND sc.colid &＃61; a.colid) THEN &＃39;√&＃39; ELSE &＃39;&＃39; END AS 主键,

b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, &＃39;PRECISION&＃39;)

AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, &＃39;Scale&＃39;), 0) AS 小数位数,

CASE WHEN a.isnullable &＃61; 1 THEN &＃39;√&＃39; ELSE &＃39;&＃39; END AS 允许空, ISNULL(e.text, &＃39;&＃39;)

AS 默认值, ISNULL(g.[value], &＃39;&＃39;) AS 字段说明, d.crdate AS 创建时间,

CASE WHEN a.colorder &＃61; 1 THEN d.refdate ELSE NULL END AS 更改时间

FROM dbo.syscolumns a LEFT OUTER JOIN

dbo.systypes b ON a.xtype &＃61; b.xusertype INNER JOIN

dbo.sysobjects d ON a.id &＃61; d.id AND d.xtype &＃61; &＃39;U&＃39; AND

d.status >&＃61; 0 LEFT OUTER JOIN

dbo.syscomments e ON a.cdefault &＃61; e.id LEFT OUTER JOIN

dbo.sysproperties g ON a.id &＃61; g.id AND a.colid &＃61; g.smallid LEFT OUTER JOIN

dbo.sysproperties f ON d.id &＃61; f.id AND f.smallid &＃61; 0

ORDER BY d.name, a.colorder

2. SqlServer数据库字典--索引.sql

以下是引用片段&＃xff1a;

SELECT TOP 100 PERCENT --a.id,

CASE WHEN b.keyno &＃61; 1 THEN c.name ELSE &＃39;&＃39; END AS 表名,

CASE WHEN b.keyno &＃61; 1 THEN a.name ELSE &＃39;&＃39; END AS 索引名称, d.name AS 列名,

b.keyno AS 索引顺序, CASE indexkey_property(c.id, b.indid, b.keyno, &＃39;isdescending&＃39;)

WHEN 1 THEN &＃39;降序&＃39; WHEN 0 THEN &＃39;升序&＃39; END AS 排序, CASE WHEN p.id IS NULL

THEN &＃39;&＃39; ELSE &＃39;√&＃39; END AS 主键, CASE INDEXPROPERTY(c.id, a.name, &＃39;IsClustered&＃39;)

WHEN 1 THEN &＃39;√&＃39; WHEN 0 THEN &＃39;&＃39; END AS 聚集, CASE INDEXPROPERTY(c.id,

a.name, &＃39;IsUnique&＃39;) WHEN 1 THEN &＃39;√&＃39; WHEN 0 THEN &＃39;&＃39; END AS 唯一,

CASE WHEN e.id IS NULL THEN &＃39;&＃39; ELSE &＃39;√&＃39; END AS 唯一约束,

a.OrigFillFactor AS 填充因子, c.crdate AS 创建时间, c.refdate AS 更改时间

FROM dbo.sysindexes a INNER JOIN

dbo.sysindexkeys b ON a.id &＃61; b.id AND a.indid &＃61; b.indid INNER JOIN

dbo.syscolumns d ON b.id &＃61; d.id AND b.colid &＃61; d.colid INNER JOIN

dbo.sysobjects c ON a.id &＃61; c.id AND c.xtype &＃61; &＃39;U&＃39; LEFT OUTER JOIN

dbo.sysobjects e ON e.name &＃61; a.name AND e.xtype &＃61; &＃39;UQ&＃39; LEFT OUTER JOIN

dbo.sysobjects p ON p.name &＃61; a.name AND p.xtype &＃61; &＃39;PK&＃39;

WHERE (OBJECTPROPERTY(a.id, N&＃39;IsUserTable&＃39;) &＃61; 1) AND (OBJECTPROPERTY(a.id,

N&＃39;IsMSShipped&＃39;) &＃61; 0) AND (INDEXPROPERTY(a.id, a.name, &＃39;IsAutoStatistics&＃39;) &＃61; 0)

ORDER BY c.name, a.name, b.keyno

3. SqlServer数据库字典--主键.外键.约束.视图.函数.存储过程.触发器.sql

以下是引用片段&＃xff1a;

SELECT DISTINCT

TOP 100 PERCENT o.xtype,

CASE o.xtype WHEN &＃39;X&＃39; THEN &＃39;扩展存储过程&＃39; WHEN &＃39;TR&＃39; THEN &＃39;触发器&＃39; WHEN &＃39;PK&＃39; THEN

&＃39;主键&＃39; WHEN &＃39;F&＃39; THEN &＃39;外键&＃39; WHEN &＃39;C&＃39; THEN &＃39;约束&＃39; WHEN &＃39;V&＃39; THEN &＃39;视图&＃39; WHEN &＃39;FN&＃39;

THEN &＃39;函数-标量&＃39; WHEN &＃39;IF&＃39; THEN &＃39;函数-内嵌&＃39; WHEN &＃39;TF&＃39; THEN &＃39;函数-表值&＃39; ELSE &＃39;存储过程&＃39;

END AS 类型, o.name AS 对象名, o.crdate AS 创建时间, o.refdate AS 更改时间,

c.text AS 声明语句

FROM dbo.sysobjects o LEFT OUTER JOIN

dbo.syscomments c ON o.id &＃61; c.id

WHERE (o.xtype IN (&＃39;X&＃39;, &＃39;TR&＃39;, &＃39;C&＃39;, &＃39;V&＃39;, &＃39;F&＃39;, &＃39;IF&＃39;, &＃39;TF&＃39;, &＃39;FN&＃39;, &＃39;P&＃39;, &＃39;PK&＃39;)) AND

(OBJECTPROPERTY(o.id, N&＃39;IsMSShipped&＃39;) &＃61; 0)

ORDER BY CASE o.xtype WHEN &＃39;X&＃39; THEN &＃39;扩展存储过程&＃39; WHEN &＃39;TR&＃39; THEN &＃39;触发器&＃39; WHEN

&＃39;PK&＃39; THEN &＃39;主键&＃39; WHEN &＃39;F&＃39; THEN &＃39;外键&＃39; WHEN &＃39;C&＃39; THEN &＃39;约束&＃39; WHEN &＃39;V&＃39; THEN &＃39;视图&＃39;

WHEN &＃39;FN&＃39; THEN &＃39;函数-标量&＃39; WHEN &＃39;IF&＃39; THEN &＃39;函数-内嵌&＃39; WHEN &＃39;TF&＃39; THEN &＃39;函数-表值&＃39;

ELSE &＃39;存储过程&＃39; END DESC

本条技术文章来源于互联网&＃xff0c;如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源&＃xff1a;php中文网