数据字典生成工具之旅(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

视图

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

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

sp_helptext fn_Spilt

  创建语句就知道了,这里提供另外一个好的工具,书写SQL和提示方面更加智能SQL Prompt,在做数据库开发时提效不只是一点点哦,这里上几张截图,有关该工具详细介绍可以参考SQL Prompt——SQL智能提示插件这篇介绍。

回到顶部

实际应用

   介绍完上面几个重要的视图以后,这里介绍一下这些视图的实际作用。

  1. 创建可重复执行的语句
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安装程序

最新安装程序

数据字典生成工具源代码

最新源代码

http://code.taobao.org/svn/DataDicPub

SVN最新源码共享地址

回到顶部

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏cloudskyme

oracle修改表字段

增加字段     alter   table   docdsp     add   dspcode   char(200)     删除字段     AL...

34910
来自专栏「3306 Pai」社区

NOT NULL列用IS NULL也能查到数据?

有没有觉得很奇怪,为什么查到了2条 dt 列值为 '0000-00-00 00:00:00' 的记录?

760
来自专栏高爽的专栏

MySQL从5.5升级到5.6,TIMESTAMP的变化

前言 前段时间,系统MySQL从5.5升级到了5.6,系统出现了大量的异常。大部分异常引起原因是由于TIMESTAMP的行为发生了变化。 TIMESTAMP在M...

2070
来自专栏指尖下的Android

二进制的运算

在计算机中存储字节是定长的,即我们8、16、32位等等,6的二进制位为110,但如果在8位计算机中是00000110,高位补零

943
来自专栏xingoo, 一个梦想做发明家的程序员

剑指OFFER之包含min函数的栈(九度OJ1522)

题目描述: 定义栈的数据结构,请在该类型中实现一个能够得到栈最小元素的min函数。 输入: 输入可能包含多个测试样例,输入以EOF结束。 对于每个测试案...

1886
来自专栏跟着阿笨一起玩NET

不允许 ASSIGNMENT 语句中包含 FOR XML 子句。

521
来自专栏技术博文

MySQL SHOW PROFILE(剖析报告)的查看

前言:SHOW PROFIL命令是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。 一、参数的开启和关闭设置 1.1 参...

3053
来自专栏技术碎碎念

mysql使用基础 sql语句(一)

命令行输入mysql -u root -p,回车再输入密码,进入mysql。 终端命令以分号作为一条语句的结束,可分为多行输入,只需在最后加上分号即可。如下图:...

34210
来自专栏salesforce零基础学习

salesforce 零基础学习(七十)使用jquery tree实现树形结构模式

项目中UI需要用到树形结构显示内容,后来尽管不需要做了,不过还是自己做着玩玩,mark一下,免得以后项目中用到。 实现树形结构在此使用的是jquery的dyna...

1816
来自专栏java相关

使用shell脚本生成数据库markdown文档

学习shell脚本编程的一次实践,通过shell脚本生成数据库的markdown文档,代码如下:

1145

扫码关注云+社区