当前位置: 首页 > 图文教程 > 数据库 > MSSQL > SQL Server数据库字典SQL语句

MSSQL
MySQL中SQL的单字节注入与宽字节注入
解决MySQL 5数据库连接超时问题
通过事务日志解决SQL Server常见四大故障
将SQL 2000日志迁移到SQL Server 2008
在应用环境中如何构造最优的数据库模式
数据库安全管理的三个经验分享
SQL Server应用程序的高级Sql注入
Oracle数据库与SQL Server数据库镜像对比
讲解SQL Server安装sp4补丁报错的解决方法
SQL Server 2005数据库批量更新解决办法
编写和优化SQL Server的存储过程
从数据行入手保护SQL Server数据安全
SQL Server数据体系和应用程序逻辑详解
网友分享：Oracle数据库开发技术经验浅谈
把你的数据库置于版本控制之下
SQL Server 2005实现数据库缓存依赖
SQL Server数据库管理常用SQL和T-SQL语句
初学者必读：提高SQL执行效率的几点建议
初探MS SQL CE+Codesmith
优化SQLServer数据库服务器内存配置的策略

No. « 59 60 61 62 »

技术文章搜索

关键字

MSSQL 中的 SQL Server数据库字典SQL语句

出处:互联网   整理: 软晨网（RuanChen.com）   发布: 2009-09-30   浏览: 134 ::
收藏到网摘: n/a

　　本文主要介绍了三个实用的SQL Server数据库字典SQL语句，数据库字典主要包括表结构(分为SQL Server 2000和SQL Server 2005)、索引和主键. 外键.约束.视图.函数.存储过程.触发器。你可以在查询分析器、企业管理器中简单执行后，快速的查出SQL Server 2000及SQL Server 2005的全部数据字典。

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

 

SELECT TOP 100 PERCENT --a.id,
CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名,
CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明,
a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id,
a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识,
CASE WHEN EXISTS
(SELECT 1
FROM dbo.sysindexes si INNER JOIN
dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键,
b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION')
AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,
CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '')
AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间,
CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END 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.status >= 0 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 AND
g.name = 'MS_Description' LEFT OUTER JOIN
dbo.sysproperties f ON d.id = f.id AND f.smallid = 0 AND
f.name = 'MS_Description'
ORDER BY d.name, a.colorder

 

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

 

SELECT TOP 100 PERCENT --a.id,
CASE WHEN a.colorder = 1 THEN d.name ELSE '' END AS 表名,
CASE WHEN a.colorder = 1 THEN isnull(f.value, '') ELSE '' END AS 表说明,
a.colorder AS 字段序号, a.name AS 字段名, CASE WHEN COLUMNPROPERTY(a.id,
a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END AS 标识,
CASE WHEN EXISTS
(SELECT 1
FROM dbo.sysindexes si INNER JOIN
dbo.sysindexkeys sik ON si.id = sik.id AND si.indid = sik.indid INNER JOIN
dbo.syscolumns sc ON sc.id = sik.id AND sc.colid = sik.colid INNER JOIN
dbo.sysobjects so ON so.name = si.name AND so.xtype = 'PK'
WHERE sc.id = a.id AND sc.colid = a.colid) THEN '√' ELSE '' END AS 主键,
b.name AS 类型, a.length AS 长度, COLUMNPROPERTY(a.id, a.name, 'PRECISION')
AS 精度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数,
CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END AS 允许空, ISNULL(e.text, '')
AS 默认值, ISNULL(g.[value], '') AS 字段说明, d.crdate AS 创建时间,
CASE WHEN a.colorder = 1 THEN d.refdate ELSE NULL END 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.status >= 0 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 AND
g.name = 'MS_Description' LEFT OUTER JOIN
dbo.sysproperties f ON d.id = f.id AND f.smallid = 0 AND
f.name = 'MS_Description'
ORDER BY d.name, a.colorder

 

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

 

SELECT TOP 100 PERCENT --a.id,
CASE WHEN b.keyno = 1 THEN c.name ELSE '' END AS 表名,
CASE WHEN b.keyno = 1 THEN a.name ELSE '' END AS 索引名称, d.name AS 列名,
b.keyno AS 索引顺序, CASE indexkey_property(c.id, b.indid, b.keyno, 'isdescending')
WHEN 1 THEN '降序' WHEN 0 THEN '升序' END AS 排序, CASE WHEN p.id IS NULL
THEN '' ELSE '√' END AS 主键, CASE INDEXPROPERTY(c.id, a.name, 'IsClustered')
WHEN 1 THEN '√' WHEN 0 THEN '' END AS 聚集, CASE INDEXPROPERTY(c.id,
a.name, 'IsUnique') WHEN 1 THEN '√' WHEN 0 THEN '' END AS 唯一,
CASE WHEN e.id IS NULL THEN '' ELSE '√' END AS 唯一约束,
a.OrigFillFactor AS 填充因子, c.crdate AS 创建时间, c.refdate AS 更改时间
FROM dbo.sysindexes a INNER JOIN
dbo.sysindexkeys b ON a.id = b.id AND a.indid = b.indid INNER JOIN
dbo.syscolumns d ON b.id = d.id AND b.colid = d.colid INNER JOIN
dbo.sysobjects c ON a.id = c.id AND c.xtype = 'U' LEFT OUTER JOIN
dbo.sysobjects e ON e.name = a.name AND e.xtype = 'UQ' LEFT OUTER JOIN
dbo.sysobjects p ON p.name = a.name AND p.xtype = 'PK'
WHERE (OBJECTPROPERTY(a.id, N'IsUserTable') = 1) AND (OBJECTPROPERTY(a.id,
N'IsMSShipped') = 0) AND (INDEXPROPERTY(a.id, a.name, 'IsAutoStatistics') = 0)
ORDER BY c.name, a.name, b.keyno

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

 

SELECT DISTINCT
TOP 100 PERCENT o.xtype,
CASE o.xtype 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 类型, 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 = c.id
WHERE (o.xtype IN ('X', 'TR', 'C', 'V', 'F', 'IF', 'TF', 'FN', 'P', 'PK')) AND
(OBJECTPROPERTY(o.id, N'IsMSShipped') = 0)
ORDER BY CASE o.xtype 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