获取SQL Server数据字典的SQL语句

这篇文章主要为大家详细介绍了获取SQL Server数据字典的SQL语句，具有一定的参考价值，可以用来参考一下。

对此感兴趣的朋友，看看idc笔记做的技术笔记！

代码如下:

CREATE VIEW dbo.vw_db_dictionary 
AS 
SELECT TOP 100 PERCENT dbo.sysobjects.name AS table_name, 
dbo.sysproperties.[value] AS table_desc, dbo.syscolumns.name AS field, 
properties.[value] AS field_desc, dbo.systypes.name AS field_type, 
dbo.syscolumns.length AS field_size, 
ISNULL(COLUMNPROPERTY(dbo.syscolumns.id, dbo.syscolumns.name, 'Scale'), 0) 
AS field_precision, dbo.syscolumns.isnullable AS nullable, 
CASE WHEN syscomments.text IS NULL 
THEN '' ELSE syscomments.text END AS default_value, 
CASE WHEN COLUMNPROPERTY(syscolumns.id, syscolumns.name, 'IsIdentity') 
= 1 THEN '√' ELSE '' END AS is_identity, 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 is_key 
FROM dbo.syscolumns INNER JOIN 
dbo.sysobjects ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN 
dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype LEFT OUTER JOIN 
dbo.sysproperties properties ON dbo.syscolumns.id = properties.id AND 
dbo.syscolumns.colid = properties.smallid LEFT OUTER JOIN 
dbo.sysproperties ON dbo.sysobjects.id = dbo.sysproperties.id AND 
dbo.sysproperties.smallid = 0 LEFT OUTER JOIN 
dbo.syscomments ON dbo.syscolumns.cdefault = dbo.syscomments.id 
WHERE (dbo.sysobjects.xtype = 'U') 
ORDER BY dbo.sysobjects.name

注：关于获取SQL Server数据字典的SQL语句的内容就先介绍到这里，更多相关文章的可以留意