数据字典生成工具之旅(8)：SQL查询表的约束默认值等信息
上一篇代码生成工具里面已经用到了读取表结构的SQL，这篇将更加详细的介绍SQL SERVER常用的几张系统表和视图！
阅读目录
系统表视图介绍
1.sys.tables(用户表)
SELECT name,object_id FROM sys.tables
上面SQL是用来查询数据库里面所有用户创建的表，name为表名，object_id为表的对象id。其中object_id的值也可以用系统函数OBJECT_ID()来取
SELECT OBJECT_ID(‘Other‘)
可以看到两者的值是一样的。
2.sys.views(用户视图)
SELECT * FROM sys.views
可以看到结果集中也是包含object_id这一列的，并且这个值也是不相同的，相当于主键列。后面会用到这一点。
3.sys.columns(列视图)
SELECT * FROM sys.columns
sys.tables和sys.columns是通过object_id这一列进行关联的。说了几个视图相信大家都有了直观的印象，会不会有这个疑问有没有这样一个视图能知道系统所有的数据对象呢，答案是肯定的。
4.sys.objects(数据对象视图)
SELECT name,object_id,type FROM sys.objects
我这里特意标红了type这一列，type常用的值及含义
| FN | 标量函数 |
| P | 存储过程 |
| PK | 主键 |
| TF | 表值函数 |
| U | 用户表 |
| V | 视图 |
sp_helptext fn_Spilt
创建语句就知道了，这里提供另外一个好的工具，书写SQL和提示方面更加智能SQL Prompt，在做数据库开发时提效不只是一点点哦，这里上几张截图，有关该工具详细介绍可以参考SQL Prompt——SQL智能提示插件这篇介绍。
实际应用
介绍完上面几个重要的视图以后，这里介绍一下这些视图的实际作用。
- 创建可重复执行的语句
IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE object_id=object_id(‘Test‘)) BEGIN CREATE TABLE Test( num INT ) END
通过判断sys.objects视图是否存在test对象来决定是否创建表，这样就算SQL一直执行都不会报错了。
2. 清空数据库表数据
要清空一个表的数据很简单，直接执行下面SQL即可。可是表多了呢，复制粘贴肯定很麻烦。这个时候sys.tables可以帮上忙了。
TRUNCATE TABLE dbo.myuser
DECLARE @Total AS INT DECLARE @i AS INT DECLARE @name AS VARCHAR(200) SELECT name,IDENTITY(INT,1,1) AS Id INTO #TempDelTable FROM sys.tables SELECT @Total=COUNT(1),@i=1 FROM #TempDelTable WHILE @i<=@Total BEGIN SELECT @name=name FROM #TempDelTable WHERE Id=@i EXEC(‘TRUNCATE TABLE ‘+@name) SELECT @i=@i+1 END DROP TABLE #TempDelTable
3. 查询表的相关信息（表中文名，字段中文名，是否主键....）
如何通过SQL来直观的查询出表的字段相关信息呢，下面提供SQL
GO IF EXISTS(SELECT 1 FROM sys.objects WHERE object_id=object_id(‘fn_DataDic‘)) BEGIN DROP FUNCTION dbo.fn_DataDic END GO CREATE FUNCTION [dbo].[fn_DataDic](@table_name VARCHAR(50)) RETURNS @Result TABLE( table_name VARCHAR(100),--表英文名-- table_name_c VARCHAR(100),--表中文名-- field_name VARCHAR(100), --列名-- field_name_c VARCHAR(100), --列名中文名-- file_sequence INT,--列顺序-- id VARCHAR(100),--表的id-- colid VARCHAR(100),--列的id-- date_type VARCHAR(50),--数据类型-- width INT,--数据宽度-- pk bit,--是否主键-- defaultvalue VARCHAR(100),--默认值-- isnullable bit, --是否可空-- isidentity bit --是否主动增长-- ) AS /************************************************************** *函数功能：查询数据库中用户表和视图的数据字典 *输入参数： @table_name：表名 如果为NULL或‘‘则查询所有的表或视图 *返回值： table_name VARCHAR(100),--表英文名-- table_name_c VARCHAR(100),--表中文名-- field_name VARCHAR(100), --列名-- field_name_c VARCHAR(100), --列名中文名-- file_sequence INT,--列顺序-- id VARCHAR(100),--表的id-- colid VARCHAR(100),--列的id-- date_type VARCHAR(50),--数据类型-- width INT,--数据宽度-- pk bit,--是否主键-- defaultvalue VARCHAR(100),--默认值-- isnullable bit, --是否可空-- isidentity bit --是否主动增长-- *2013-03-29__dudj__创建 ***************************************************************/ BEGIN IF @table_name IS NULL OR LTRIM(RTRIM(@table_name))=‘‘ BEGIN INSERT INTO @Result SELECT T.name AS table_name, ‘‘ AS table_name_c, C.name AS field_name, ‘‘ AS field_name_c, C.colorder AS file_sequence, C.id AS id, C.colid AS colid, TYPE_NAME(C.xtype) AS date_type, C.length AS width, convert(bit,case when exists(select 1 from sysobjects where xtype=‘PK‘ and parent_obj=c.id and name in ( select name from sysindexes where indid in( select indid from sysindexkeys where id = c.id and colid=c.colid))) then 1 else 0 end) AS pk, ISNULL(CM.text,‘‘) AS defaultvalue, ISNULL(C.isnullable,1) AS isnullable, ISNULL(COLUMNPROPERTY(c.id,c.name,‘IsIdentity‘),0) AS isidentity FROM sysobjects AS T LEFT JOIN syscolumns AS C ON c.id=T.id LEFT JOIN syscomments CM on c.cdefault=CM.id WHERE T.xtype IN (‘U‘,‘V‘) END ELSE BEGIN INSERT INTO @Result SELECT T.name AS table_name, ‘‘ AS table_name_c, C.name AS field_name, ‘‘ AS field_name_c, C.colorder AS file_sequence, C.id AS id, C.colid AS colid, TYPE_NAME(C.xtype) AS date_type, C.length AS width, convert(bit,case when exists(select 1 from sysobjects where xtype=‘PK‘ and parent_obj=c.id and name in ( select name from sysindexes where indid in( select indid from sysindexkeys where id = c.id and colid=c.colid))) then 1 else 0 end) AS pk, ISNULL(CM.text,‘‘) AS defaultvalue, ISNULL(C.isnullable,1) AS isnullable, ISNULL(COLUMNPROPERTY(c.id,c.name,‘IsIdentity‘),0) AS isidentity FROM sysobjects AS T LEFT JOIN syscolumns AS C ON c.id=T.id LEFT JOIN syscomments CM on c.cdefault=CM.id WHERE T.xtype IN (‘U‘,‘V‘) AND (T.NAME=@table_name) END /*更新表名中文，列名中文说明*/ UPDATE @Result SET table_name_c= ( SELECT CONVERT(VARCHAR(100),P.VALUE) FROM sys.extended_properties AS P WHERE P.minor_id=0 AND P.major_id=id ),field_name_c = ( SELECT CONVERT(VARCHAR(100),P.VALUE) FROM sys.extended_properties AS P WHERE P.major_id = id AND P.minor_id = colid ) RETURN END GO
先创建一个视图，方便以后重复使用，创建好以后这样使用
SELECT * FROM dbo.fn_DataDic(‘myuser‘)
本章总结
通过几个系统视图的介绍和实际例子结合，完成了表的详细信息的取数，数据字典生成工具和代码生成工具里面都有用到相关内容。介绍到这里或许你会对上图中的表中文名和列中文名怎么出来的不明白。
这些信息是存储在拓展属性这里的，可以通过SELECT * FROM sys.extended_properties 来进行查询。
工具源代码下载
目前总共有经过了七个版本的升级，现在提供最新版本的下载地址
| 数据字典生成工具V2.0安装程序 | 最新安装程序 | |
| 数据字典生成工具源代码 | 最新源代码 | |
学习使用
如果你使用了该工具，或者想学习该工具，欢迎加入这个小组，一起讨论数据字典生成工具、把该工具做的更强，更方便使用，一起加入147425783 QQ群。
更多数据字典生成工具资料请点击数据字典生成工具专题。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
