数据字典生成工具之旅(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 条评论
登录 后参与评论

相关文章

来自专栏逸鹏说道

维护索引(3)——通过重建索引提高性能

前言: 重建一个索引只是在内部删除并重建索引,使得碎片消失、统计信息更新、物理顺序重新排列组织。它会压缩数据页,按照填充因子填充适当的数据。如果有需要,也会添加...

2334
来自专栏Dato

浅谈 DML、DDL、DCL的区别

一、DML DML(data manipulation language)数据操纵语言:     就是我们最经常用到的 SELECT、UPDATE、INSERT...

3457
来自专栏数据和云

深入解析:你听说过Oracle数据库的更新重启动吗?

杨廷琨 云和恩墨高级咨询顾问, ITPUB Oracle 数据库管理版版主 ,人称 “杨长老”,十数年如一日坚持进行 Oracle 技术研究与写作,号称 ...

734
来自专栏Vamei实验室

Python标准库14 数据库 (sqlite3)

Python自带一个轻量级的关系型数据库SQLite。这一数据库使用SQL语言。SQLite作为后端数据库,可以搭配Python建网站,或者制作有数据存储需求的...

1649
来自专栏乐沙弥的世界

使用 DBMS_PROFILER 定位 PL/SQL 瓶颈代码

      对于SQL的优化,可以借助于SQL tuning advisor实现自动SQL优化与调整。而对于PL/SQL代码而言,既有SQL代码,又有PL/SQ...

611
来自专栏一个会写诗的程序员的博客

SQL中的DML、DDL以及DCL是什么?

DML(data manipulation language)是数据操纵语言:它们是SELECT、UPDATE、INSERT、DELETE,就象它的名字一样,这...

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

SQL Server 2008的新语句merge

SQL Server 2008将包含用于合并两个行集(rowset)数据的新句法。根据一个源数据表对另一个数据表进行确定性的插入、更新和删除这样复杂的操作,运用...

602
来自专栏智能合约

数据插入失败引发的主键auto_increment问题

1463
来自专栏weixuqin 的专栏

MySQL 学习笔记(三):完整性和触发器设计

1014
来自专栏数据和云

View Merge 在安全控制上的变化,是 BUG 还是增强 ?

什么是 View Merge View Merge 是 12C 引入的新特性,也是一种优化手段。当查询中引用了 View 或 inline view 时,优化器...

3257

扫码关注云+社区