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

相关文章

来自专栏守望轩

VS 2010 Database tool 查询分析器的小困扰

昨天用Visual Studio 2010 做一个程序,使用的是sql server 2005的数据库,使用Visual Studio 2010 的Datab...

19190
来自专栏乐沙弥的世界

Oracle 测试常用表BIG_TABLE

创建测试用表,DBA经常用到,通常都是基于dba_objects来创建的比较多。本文根据Tom大师的big_table进行了整理,供大家参考。

16820
来自专栏我的博客

Python-MySQL查询函数使用示例

# -*- coding: UTF-8 -*- import MySQLdb import time #警告信息try except是无法捕捉的 fro...

36550
来自专栏数据和云

层层升入:SQL极限调优之一次更新操作的N种优化可能

杨廷琨,网名 yangtingkun 云和恩墨技术总监,Oracle ACE Director,ACOUG 核心专家 最近进行了一次更新操作,整个处理和优化的过...

33380
来自专栏文渊之博

优化SQLServer——表和分区索引(二)

简介     之前一篇简单的介绍了语法和一些基本的概念,隔了一段时间,觉得有必要细致的通过实例来总结一下这部分内容。如之前所说,分区就是讲大型的对象(表)分成更...

23560
来自专栏互联网开发者交流社区

数据定义: CREATE、DROP、ALTER

15220
来自专栏性能与架构

Mysql Query Cache的基本原理

Query Cache是根据SQL语句来cache的,一个SQL查询如果以select开头,那么MySQL将尝试对其进行缓存 每个Cache都是以完整的SQL...

36950
来自专栏帘卷西风的专栏

关于mysql存储过程创建动态表名及参数处理

转载请注明出处:帘卷西风的专栏(http://blog.csdn.net/ljxfblog) 

20630
来自专栏PHP在线

性能调优之MYSQL高并发优化

一、数据库结构的设计 表的设计具体注意的问题: 1、数据行的长度不要超过8020字节,如果超过这个长度的话在物理页中这条数据会占用两行从而造成存储碎片,降低查询...

54080
来自专栏极客慕白的成长之路

MySQL从安装到使用

Columns 列;Indexes 索引;Views 视图;Events 事件;Fields 字段;

10040

扫码关注云+社区

领取腾讯云代金券