获取MSSQL数据字典的SQL语句
时间:2024-01-20 11:35:16
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
标签:MSSQL,数据字典,SQL语句
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
python实现beta分布概率密度函数的方法
2021-08-02 21:39:16
![](https://img.aspxhome.com/file/2023/9/93449_0s.jpg)
ASP压缩ACCESS数据库实例
2009-01-19 11:47:00
JavaScript[对象.属性]集锦
2020-07-08 18:05:45
在pandas中遍历DataFrame行的实现方法
2022-02-16 13:13:26
![](https://img.aspxhome.com/file/2023/7/75867_0s.jpg)
SQL Server 分页查询存储过程代码
2024-01-21 04:53:40
这样写python注释让代码更加的优雅
2021-01-07 09:09:12
Python之基础函数案例详解
2023-07-09 19:43:24
![](https://img.aspxhome.com/file/2023/8/87808_0s.png)
Python+Pika+RabbitMQ环境部署及实现工作队列的实例教程
2021-08-19 13:13:13
![](https://img.aspxhome.com/file/2023/8/123688_0s.png)
[翻译]标记语言和样式手册 Chapter 14 图片替换
2008-02-18 12:56:00
![](https://img.aspxhome.com/file/UploadPic/20082/18/2008218132218368s.jpg)
微信小程序图片左右摆动效果详解
2023-08-09 13:34:31
![](https://img.aspxhome.com/file/2023/6/56026_0s.gif)
对抗MySQL数据库解密高手
2008-12-25 13:14:00
分享下GET和POST的真正区别
2023-10-19 23:23:09
python scipy.spatial.distance 距离计算函数
2023-01-25 08:55:23
解决PHP 7编译安装错误:cannot stat ‘phar.phar’: No such file or directory
2023-09-05 06:07:44
![](https://img.aspxhome.com/file/2023/2/55632_0s.jpg)
显示你个性的鼠标指针
2011-06-14 09:41:41
ASP模拟MVC模型的编程方式
2008-10-15 14:51:00
Golang的继承模拟实例
2024-05-08 10:23:14
一文带大家了解Go语言中的内联优化
2024-02-19 15:47:19
使用Python和百度语音识别生成视频字幕的实现
2022-02-28 23:12:42
![](https://img.aspxhome.com/file/2023/7/127757_0s.png)
利用css的clear属性搞定广告文字环绕效果
2008-05-24 13:48:00