查看数据库表的基本信息

日期： 2010-09-05 作者：kerrycode 来源：TechTarget中国 英文

　　用SQL Doc生成数据库字典文档的时候，突然发现有字段描叙(Description)这项内容,以前一直没有注意过，故特意研究了一下,结果越挖越深，就写了这篇文章。

　　以前在做数据库脚本开发时，新建表时，对各个字段的描叙要么是记录在文档里面，要么自己建一个表，来保存这些内容，以便日后开发、维护的方便。其实这些信息完全可以放在数据库自己的系统视图里面。

　　对字段的说明、描述一般都放在系统视图sys.extended_properties中，例如(表dbo.Employee的字段Department的说明)

　　SELECT * FROM dbo.Employee
　　SELECT * FROM sys.extended_properties

　　其中 当class =1时，major_id它的值是dbo.Employee的id，minor_id是Department的id(详细信息参见MSDN)，如下图所示

　　sql查询

　　SELECT OBJECT_ID(N’dbo.Employee’)
　　SELECT column_id FROM sys.columns
　　WHERE object_id = OBJECT_ID(‘dbo.Employee’)
　　AND name = ‘Department’

　　sql查询

　　其实在MSSMS 管理器中，选中要添加字段说明的表，单击右键——》修改(08是设计)，如下图所示，增加后，保存。就会在sys.extended_properties里添加相应的记录。

　　sql表设计

　　当然你也可以用脚本命令添加数据库表的字段说明

　　EXEC sp_addextendedproperty N’MS_Description’, N’雇员名称’, ‘SCHEMA’, N’dbo’, ‘TABLE’, N’Employee’, ‘COLUMN’, N’EmployeeName’

　　如果已经存在刚才记录，你再执行上面这段脚本，就会提示：

　　消息 15233，级别 16，状态 1，过程 sp_addextendedproperty，第 38 行

　　无法添加属性。’dbo.Employee.EmployeeName’ 已存在属性 ‘MS_Description’。

　　下面看看工具生成的文档，工具生成这些信息肯定是数据库里存有对象的这些信息，下面我们来看看这些信息都是从何而来吧

　　sql工具生成文档

　　这里先列举一些保存表信息的系统表、视图吧，可能有些遗漏了，实在太多了，要仔细把这些全部列举出来还得花费一番功夫

　　SELECT * FROM sys.columns
　　–为每个表和视图中的每列返回一行，并为数据库中的存储过程的每个参数返回一行。
　　SELECT * FROM syscolumns
　　–每个表对象的信息
　　SELECT * FROM sys.tables
　　SELECT * FROM sysobjects
　　–在数据库中创建的每个用户定义的架构范围内的对象的信息
　　SELECT * FROM sys.objects
　　–数据库实例中的每个数据库的信息
　　SELECT * FROM sys.databases
　　–系统数据类型
　　SELECT * FROM sys.types
　　–含数据库中每个视图、规则、默认值、触发器、CHECK 约束、DEFAULT 约束和存储过程的项
　　SELECT * FROM dbo.syscomments
　　–保存表的自增列信息
　　SELECT * FROM sys.identity_columns

　　下面来看看属性那栏的信息保存在那些表里面。如果表是数据库的默认排序规则，就可以用下面脚本。

　　SELECT create_date AS Created ,
　　modify_date AS Last Modified,
　　( SELECT collation_name
　　FROM sys.databases
　　WHERE name = ‘MyAssistant’
　　) AS collation_name
　　FROM SYS.tables
　　WHERE NAME = ‘Employee’

　　如果用某个列的排序规则可用下面的脚本

　　SELECT create_date AS Created,
　　modify_date AS Last Modified,
　　( SELECT DISTINCT
　　collation
　　FROM syscolumns
　　WHERE id = OBJECT_ID(N’dbo.Employee’)
　　AND collation IS NOT NULL
　　AND name =’EmployeeName’
　　) AS collation_name
　　FROM sys.tables
　　WHERE NAME = ‘Employee’

　　查看数据库的排序规则可以从 sys.databases查看，而表的某个列的排序规则信息保存在syscolumns里面。上图的Heap， Row Count信息我还不知是从哪里来的。

　　接下来看看Cloumns信息吧

　　SELECT
　　C.Name AS FieldName,
　　T.Name AS DataType,
　　CASE WHEN C.Max_Length = -1 THEN ‘Max’ ELSE CAST(C.Max_Length AS VARCHAR) END AS Max_Length,
　　CASE WHEN C.is_nullable = 0 THEN ‘×’ ELSE ‘√’ END AS Is_Nullable,
　　C.is_identity,
　　ISNULL(M.text, ”) AS DefaultValue,
　　ISNULL(P.value, ”) AS FieldComment
　　FROM sys.columns C
　　INNER JOIN sys.types T ON C.system_type_id = T.user_type_id
　　LEFT JOIN dbo.syscomments M ON M.id = C.default_object_id
　　LEFT JOIN sys.extended_properties P ON P.major_id = C.object_id AND C.column_id = P.minor_id
　　WHERE C.[object_id] = OBJECT_ID(‘dbo.Employee’)
　　ORDER BY C.Column_Id ASC

　　如图所示，得到结果与文档还是有些区别，我通过该脚本实现与文档一致的时候，怎么也找不到nvarchar(30)的30，这个值的出处，后来才发现它其实就是nvarchar的max_length 的一半。

　　数据库表结构设计

　　修改脚本如下所示

　　SELECT
　　C.Name AS FieldName,
　　CASE WHEN T.Name =’nvarchar’ THEN
　　T.name +'(‘ + CAST(C.max_length/2 AS VARCHAR) +’)’
　　ELSE T.name END AS DataType,
　　CASE WHEN C.Max_Length = -1 THEN ‘Max’ ELSE CAST(C.Max_Length AS VARCHAR) END AS Max_Length,
　　CASE WHEN C.is_nullable = 0 THEN ‘×’ ELSE ‘√’ END AS Is_Nullable,
　　ISNULL(CAST(I.seed_value AS VARCHAR) + ‘-‘ + CAST(I.increment_value AS VARCHAR), ”) AS is_identity,
　　ISNULL(M.text, ”) AS DefaultValue,
　　ISNULL(P.value, ”) AS FieldComment
　　FROM sys.columns C
　　INNER JOIN sys.types T ON C.system_type_id = T.user_type_id
　　LEFT JOIN dbo.syscomments M ON M.id = C.default_object_id
　　LEFT JOIN sys.extended_properties P ON P.major_id = C.object_id AND C.column_id = P.minor_id
　　LEFT JOIN sys.identity_columns I ON I.column_id= C.column_id AND C.object_id = I.object_id
　　WHERE C.[object_id] = OBJECT_ID(‘dbo.Employee’)
　　ORDER BY C.Column_Id ASC

　　接下来看看Perssion信息来自何处。 首先我们来看看赋与、收回权限的脚本(我是在sa账号下运行的)

　　DENY SELECT ON [dbo].[Employee] TO [Kerry]
　　GO
　　GO
　　DENY DELETE ON [dbo].[Employee] TO [Kerry]
　　GO
　　REVOKE DELETE ON [dbo].[Employee] TO [Kerry]
　　GO
　　REVOKE SELECT ON [dbo].[Employee] TO [Kerry]
　　GO

　　那么这些权限信息保存在那个系统表或系统视图中，我查了很多资料，还是没有查到，呵呵，希望有知道的告诉一声。但是可以同过系统函数和系统存储过程得到一些相关的权限设置信息。

　　1：系统存储过程 sp_table_privileges， 它返回指定的一个或多个表的表权限(如 INSERT、DELETE、UPDATE、SELECT、REFERENCES)的列表，表具体参见(MSDN)。

　　2：系统函数 fn_my_permissions 返回有效授予主体对安全对象的权限的列表，表具体参见(MSDN)

　　EXEC sp_table_privileges @table_name = ‘Employee’;

　　EXEC sp_table_privileges @table_name =’Employee’ , @table_owner =’dbo’

　　sql查询结果

　　SELECT *
　　FROM fn_my_permissions(‘dbo.Employee’, ‘OBJECT’)
　　ORDER BY subentity_name, permission_name ;

　　select 查询

　　–查看用户Kerry的有效权限

　　SELECT * FROM fn_my_permissions(‘Kerry’, ‘USER’);

　　sql查看用户权限

　　再来看看SQL Srcipt，好像没有那个系统表、系统视图保存创建表的脚本(如果有的话，算我孤陋寡闻了)，也不能通过SP_HELPTEXT来得到(存储过程可以)，在

　　网上搜索了下大概有SMO 方式和存储过程来实现的，SMO方式我还没来得及验证，存储过程倒是找到一个(本来打算自己尝试下的。呵呵，那这篇文章得耗上好长时间了，等写完了，自己再写个试试)，下面的存储过程是我在http://edu.codepub.com/2009/0603/5408.php这里搜索到，也不知道原创作者是谁。

If object_id(‘up_CreateTable’) Is Not Null
    Drop Proc up_CreateTable
Go
/* 生成建表脚本(V2.0)  OK_008 2009-5-18 */
Create Proc up_CreateTable
(
    @objectList nvarchar(max)=null
)
–With ENCRYPTION
As
/*  参数说明：
    @objectList 对象列表，对象之间使用”,”隔开
    
    改存储过程生成的建表脚本，包含Column,Constraint,Index
*/
Set Nocount On
    Declare @sql nvarchar(max),
            @objectid int,
            @id int,
            @Rowcount int,
            @ObjectName sysname,
            @Enter nvarchar(2),
            @Tab nvarchar(2)

    Select     @Enter=Char(13)+Char(10),
            @Tab=Char(9)    

    Declare @Tmp Table(name sysname)
    
    If @objectList>”
    Begin
        Set @sql=’Select N”’+Replace(@objectList,’,’,”’ Union All Select N”’)+””
        Insert Into @Tmp (name) Exec(@sql)

        Set @sql=null
        Select @sql=Isnull(@sql+’,’,”)+name 
            From @Tmp As a
            Where Not Exists(Select 1 From sys.objects Where type=’U’ And name=a.name)

        If @sql>”
        Begin
            Set @sql=’发现无效的表名: ‘+@sql
            Raiserror 50001 @sql
            Return(1)
        End
    End

    If object_id(‘tempdb..#Objects’) Is Not Null
        Drop Table #Objects
    
    If object_id(‘tempdb..#Columns’) Is Not Null
        Drop Table #Columns    
    
    Create Table #Objects(id int Identity(1,1) Primary Key,object_id int,name sysname)

     
    ;With t As 
    (
    Select Object_id,Convert(int,0) As LevelNo,name As object_name
            From sys.objects a 
            Where Type=’U’ And is_ms_shipped=0 And Not Exists(Select 1 From sys.foreign_keys Where referenced_object_id=a.object_id)
    Union All
    Select a.referenced_object_id As Object_id,b.LevelNo+1 As LevelNo,c.name As object_name
        From sys.foreign_keys a 
            Inner Join t b On b.object_id=a.parent_object_id
            Inner Join sys.objects c On c.object_id=a.referenced_object_id And c.is_ms_shipped=0
    )
    Insert Into #Objects(object_id,name)
        Select a.object_id,object_name
            From t a
            Where    Not Exists(Select 1 From t Where object_id=a.object_id And LevelNo>a.LevelNo) And
                    Not Exists(Select 1 From sys.extended_properties Where major_id=a.object_id And minor_id=0 And class=1 And Name=N’microsoft_database_tools_support’)
                    And (Exists(Select 1 From @Tmp Where name=a.object_name) Or Not Exists(Select 1 From @Tmp))
            Group By object_id,object_name,LevelNo
            Order By LevelNo Desc

    Set @Rowcount=@@Rowcount
    If @Rowcount=0
    Begin
        Raiserror 50001 N’没有可以生产脚本的表!’
        Return(1)
    End

    –Column
    Select    a.object_id,
            a.column_id As Seq,
            Cast(1 As tinyint) As DefinitionType,
            Quotename(a.name)+Char(32)+ c.name +
            Case 
                When a.user_type_id In (231,239) Then ‘(‘+Case a.max_length When -1 Then ‘Max’ Else Rtrim(a.max_length/2) End +’)’
                When a.user_type_id In (62,165,167,173,175) Then ‘(‘+Case a.max_length When -1 Then ‘Max’ Else Rtrim(a.max_length) End+’)’
                When a.user_type_id In (106,108) Then ‘(‘+Rtrim(a.[precision])+’,’+Rtrim(a.scale)+’)’ 
                Else ”
            End
            + Char(32)+
            Case a.is_rowguidcol When 1 Then ‘Rowguidcol ‘ Else ” End +
            Case a.is_identity When 1 Then ‘Identity(‘+Cast(d.seed_value As nvarchar(10))+’,’+Cast(d.increment_value As nvarchar(10))+’) ‘ Else ” End+ 
            Case a.is_nullable When 1 Then ‘Null ‘ Else ‘Not Null ‘ End+
            Isnull(‘Constraint ‘+Quotename(e.name)+’ Default(‘+e.definition+’)’,”) As definition

            Into #Columns
        From sys.columns As a
            Inner Join #Objects As b On b.object_id=a.object_id
            Inner Join sys.types As c On c.user_type_id=a.user_type_id
            Left Outer Join sys.identity_columns As d On d.object_id=a.object_id And d.column_id=a.column_id And a.is_identity=1
            Left Outer Join sys.Default_constraints As e On e.object_id=a.default_object_id And e.parent_column_id=a.column_id

        Create Nonclustered Index IX_#Columns_object_id On #Columns(object_id Asc)

        –Constraint
        Insert Into #Columns
    
        Select    a.parent_object_id As object_id,
                Row_number() Over(Partition By a.parent_object_id Order By Case a.type When ‘PK’ Then 1 When ‘C’ Then 2 Else 3 End)As Seq,
                2 As DefinitionType,
                ‘Alter Table ‘+Quotename(object_name(a.parent_object_id)) +’ Add Constraint ‘+Quotename(a.name)+
                Case a.type 
                    When ‘PK’ Then ‘ Primary Key ‘+Case When Exists(Select 1 From sys.indexes Where object_id=a.parent_object_id And is_primary_key=1 And type=1) Then N’Clustered ‘ Else N’Nonclustered ‘ End+
                                                ‘(‘+Stuff((Select ‘,’+Quotename(c1.Name)+Case a1.is_descending_key When 1 Then ‘ Desc’ Else ‘ Asc’ End
                                                        From sys.index_columns As a1
                                                            Inner Join sys.indexes As b1 On b1.object_id=a1.object_id And b1.index_id=a1.index_id And b1.is_primary_key=1
                                                            Inner Join sys.columns As c1 On c1.object_id=a1.object_id And c1.column_id=a1.column_id
                                                        Where a1.object_id=a.parent_object_id 
                                                        For Xml Path(”)
                                                    ),1,1,”)+
                                                ‘)’
                    When ‘F’ Then ‘ Foreign Key (‘+Stuff((Select ‘,’+Quotename(b1.Name)
                                                        From sys.foreign_key_columns As a1
                                                            Inner Join sys.columns As b1 On b1.object_id=a1.parent_object_id And b1.column_id=a1.parent_column_id
                                                        Where a1.constraint_object_id=a.object_id 
                                                        Order By a1.constraint_column_id
                                                        For Xml Path(”)
                                                    ),1,1,”)+
                                                ‘) References ‘+(Select Quotename(object_name(referenced_object_id)) From  sys.foreign_keys Where object_id=a.object_id)+
                                                ‘ (‘
                                                    +Stuff((Select ‘,’+Quotename(b1.Name)
                                                        From sys.foreign_key_columns As a1
                                                            Inner Join sys.columns As b1 On b1.object_id=a1.referenced_object_id And b1.column_id=a1.referenced_column_id
                                                        Where a1.constraint_object_id=a.object_id 
                                                        Order By a1.constraint_column_id
                                                        For Xml Path(”)
                                                    ),1,1,”)+
                                                ‘)’
                    When ‘UQ’ Then ‘ Unique’+(Select Case a1.type When 1 Then ‘ Clustered’ Else ‘ Nonclustered’ End
                                                    From sys.indexes As a1
                                                    Where a1.object_id=a.parent_object_id 
                                                                And Exists(Select 1 From sys.key_constraints Where object_id=a.object_id And parent_object_id=a1.object_id And unique_index_id=a1.index_id)
                                               )+                        
                                                ‘(‘+Stuff((Select ‘,’+Quotename(c1.Name)+Case a1.is_descending_key When 1 Then ‘ Desc’ Else ‘ Asc’ End
                                                        From sys.index_columns As a1
                                                            Inner Join sys.indexes As b1 On b1.object_id=a1.object_id And b1.index_id=a1.index_id And b1.is_unique_constraint=1
                                                            Inner Join sys.columns As c1 On c1.object_id=a1.object_id And c1.column_id=a1.column_id
                                                        Where a1.object_id=a.parent_object_id 
                                                                And Exists(Select 1 From sys.key_constraints Where object_id=a.object_id And parent_object_id=a1.object_id And unique_index_id=a1.index_id)
                                                        For Xml Path(”)
                                                    ),1,1,”)+
                                                ‘)’
                    When ‘C’ Then ‘ Check’ +(Select definition From sys.check_constraints Where object_id=a.object_id)
                    Else ”
                End As definition

            From sys.objects As a
            Where a.type In(‘PK’,’F’,’C’,’UQ’)
                    And Exists(Select 1  From #Objects Where object_id=a.parent_object_id)

        –Index
        Insert Into #Columns
        Select    a.object_id ,
                a.index_id As Seq,
                3 As DefinitionType,
                ‘Create ‘+Case a.is_unique When 1 Then ‘Unique ‘ Else ” End+
                Case a.type When 1 Then ‘Clustered ‘ Else ‘Nonclustered ‘ End+
                ‘Index ‘+Quotename(a.name)+’ On ‘+Quotename(b.name)+
                                        ‘ (‘+Stuff((Select ‘,’+Quotename(b1.Name)+Case a1.is_descending_key When 1 Then ‘ Desc’ Else ‘ Asc’ End
                                                        From sys.index_columns As a1
                                                            Inner Join sys.columns As b1 On b1.object_id=a1.object_id And b1.column_id=a1.column_id
                                                        Where a1.object_id=a.object_id And a.index_id=a1.index_id And a1.is_included_column=0
                                                        For Xml Path(”)
                                                    ),1,1,”)+
                                        ‘)’+
                                        Isnull(‘ Include(‘+Stuff((Select ‘,’+Quotename(b1.Name)
                                                        From sys.index_columns As a1
                                                            Inner Join sys.columns As b1 On b1.object_id=a1.object_id And b1.column_id=a1.column_id
                                                        Where a1.object_id=a.object_id And a.index_id=a1.index_id And a1.is_included_column=1
                                                        For Xml Path(”)
                                                    ),1,1,”)+
                                        ‘)’,”)
                    As definition
            From sys.indexes As a
                Inner Join #Objects As b On b.object_id=a.object_id
            Where a.type>0
                    And Not Exists(Select 1 From sys.key_constraints Where parent_object_id=a.object_id And unique_index_id=a.index_id)

        

        –Print
        

        Print ‘Use ‘+Quotename(db_name())+@Enter+’Go’+@Enter+’/* 创建表结构 Andy ‘+Convert(nvarchar(10),Getdate(),120)+’*/’+@Enter

        Set @id=1
        While @id<=@Rowcount
        Begin
            Select @objectid=object_id,@ObjectName=name From #Objects Where id=@id

            Set @Sql=@Enter+’–(‘+Rtrim(@id)+’/’+Rtrim(@Rowcount)+’) ‘+@ObjectName+@Enter+’If object_id(”’+Quotename(@ObjectName)+”’) Is Null’+@Enter+’Begin’+@Enter+@Tab+
                    ‘Create Table ‘+Quotename(@ObjectName)+@Enter+@Tab+'(‘+@Enter
            Select @Sql=@Sql+@Tab+@Tab+definition+’,’+@Enter
                From #Columns 
                Where object_id=@objectid 
                        And DefinitionType=1
                Group By Seq,definition
                Order By Seq
            Set @sql=Substring(@sql,1,Len(@sql)-3)+@Enter+@Tab+’)’+@Enter
            Select @Sql=@Sql+@Tab+definition+@Enter
                From #Columns 
                Where object_id=@objectid 
                        And DefinitionType>1
                Group By DefinitionType,Seq,definition
                Order By Seq

            Print Substring(@sql,1,Len(@sql)-2)+@Enter+’End’
            Set @id=@id+1
        End

        Print ‘Go’

    Drop Table #Columns
    Drop Table #Objects
Go

我们一直都在努力坚持原创.......请不要一声不吭，就悄悄拿走。

我原创，你原创，我们的内容世界才会更加精彩！

【所有原创内容版权均属TechTarget，欢迎大家转发分享。但未经授权，严禁任何媒体（平面媒体、网络媒体、自媒体等）以及微信公众号复制、转载、摘编或以其他方式进行使用。】

微信公众号

TechTarget微信公众号二维码

TechTarget

官方微博

TechTarget中国官方微博二维码

TechTarget中国

电子邮件地址不会被公开。 必填项已用*标注

敬请读者发表评论，本站保留删除与本文无关和不雅评论的权力。

作者

kerrycode
kerrycode

相关推荐