數據庫表的基本信息，你真的都了解嗎？

作者：瀟湘隱者 2010-09-03 12:20:54

我們要講的正是最基礎的數據庫表的基本信息，這些知識你真的全部都了解了嗎？還是帶著疑問來看本文吧。

用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)，如下圖所示

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

其實在MSSMS 管理器中，選中要添加字段說明的表，單擊右鍵——》修改（08是設計），如下圖所示，增加后，保存。就會在sys.extended_properties里添加相應的記錄。

當然你也可以用腳本命令添加數據庫表的字段說明

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'。

下面看看工具生成的文檔，工具生成這些信息肯定是數據庫里存有對象的這些信息，下面我們來看看這些信息都是從何而來吧

這里先列舉一些保存表信息的系統表、視圖吧，可能有些遺漏了，實在太多了，要仔細把這些全部列舉出來還得花費一番功夫

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'

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

--查看用戶Kerry的有效權限  
SELECT * FROM fn_my_permissions('Kerry', 'USER');

再來看看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 aa1.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 aa1.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 aa1.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 aa1.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 aa1.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 aa1.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 aa1.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

原文標題：數據庫表的基本信息，你知道嗎？

鏈接：http://www.cnblogs.com/kerrycode/archive/2010/09/03/1816611.html

責任編輯：彭凡來源：博客園

數據庫

成人免费xxxxx在线视频软件_久久精品久久久_亚洲国产精品久久久_天天色天天色_亚洲人成一区_欧美一级欧美三级在线观看

數據庫表的基本信息，你真的都了解嗎？