获取MSSQL数据字典的SQL语句
程序员文章站
2023-01-04 17:26:44
复制代码 代码如下:create view dbo.vw_db_dictionary as select top 100 percent dbo.sysobjects.na...
复制代码 代码如下:
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
上一篇: python求素数示例分享
推荐阅读
-
SQL Server 数据库管理常用的SQL和T-SQL语句
-
用SQL语句查询数据库中某一字段下相同值的记录方法
-
关于mysql中删除重复记录,并保留重复数据中的一条数据的SQL语句的深入理解
-
SQL数据库Select语句的基本语法、条件查询
-
SQLSERVER 2005中使用sql语句对xml文件和其数据的进行操作(很全面)
-
数据库中的sql完整性约束语句解析
-
MySQL在大数据、高并发场景下的SQL语句优化和"最佳实践"
-
Oracle数据库中常用的SQL语句整理
-
iOS开发中使用SQL语句操作数据库的基本用法指南
-
数据库SQL实战题:获取员工其当前的薪水比其manager当前薪水还高的相关信息(教程)