数据字典生成工具之旅(8)：SQL查询表的约束默认值等信息

用户1168362

发布于 2018-01-05 15:19:11

1K0

发布于 2018-01-05 15:19:11

文章被收录于专栏：.net core新时代

上一篇代码生成工具里面已经用到了读取表结构的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	视图

5.sp_helptext(查看函数，视图，存储过程创建语句的系统存储过程)

知道一个存储过程名称，如何找到这个存储过程的创建语句呢，别着急这个时候sp_helptext派上用场了，请看下面SQL，其中fn_Spilt为函数名称

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

先创建一个视图，方便以后重复使用，创建好以后这样使用