數(shù)據(jù)庫表的基本信息，你知道嗎？
當前位置：點晴教程→知識管理交流
→『 技術文檔交流 』
用SQL Doc生成數(shù)據(jù)庫字典文檔的時候，突然發(fā)現(xiàn)有字段描敘(Description)這項內(nèi)容,以前一直沒有注意過，故特意研究了一下,結(jié)果越挖越深，就寫了這篇文章。 以前在做數(shù)據(jù)庫腳本開發(fā)時，新建表時，對各個字段的描敘要么是記錄在文檔里面，要么自己建一個表，來保存這些內(nèi)容，以便日后開發(fā)、維護的方便。其實這些信息完全可以放在數(shù)據(jù)庫自己的系統(tǒng)視圖里面。 對字段的說明、描述一般都放在系統(tǒng)視圖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里添加相應的記錄。
當然你也可以用腳本命令添加數(shù)據(jù)庫表的字段說明 EXEC sp_addextendedproperty N'MS_Description', N'雇員名稱', 'SCHEMA', N'dbo', 'TABLE', N'Employee', 'COLUMN', N'EmployeeName' 如果已經(jīng)存在剛才記錄，你再執(zhí)行上面這段腳本，就會提示： 消息 15233，級別 16，狀態(tài) 1，過程 sp_addextendedproperty，第 38 行 無法添加屬性。'dbo.Employee.EmployeeName' 已存在屬性 'MS_Description'。 下面看看工具生成的文檔，工具生成這些信息肯定是數(shù)據(jù)庫里存有對象的這些信息，下面我們來看看這些信息都是從何而來吧  這里先列舉一些保存表信息的系統(tǒng)表、視圖吧，可能有些遺漏了，實在太多了，要仔細把這些全部列舉出來還得花費一番功夫 代碼 SELECT * FROM sys.columns --為每個表和視圖中的每列返回一行，并為數(shù)據(jù)庫中的存儲過程的每個參數(shù)返回一行。 SELECT * FROM syscolumns --每個表對象的信息 SELECT * FROM sys.tables SELECT * FROM sysobjects --在數(shù)據(jù)庫中創(chuàng)建的每個用戶定義的架構范圍內(nèi)的對象的信息 SELECT * FROM sys.objects --數(shù)據(jù)庫實例中的每個數(shù)據(jù)庫的信息 SELECT * FROM sys.databases --系統(tǒng)數(shù)據(jù)類型 SELECT * FROM sys.types --含數(shù)據(jù)庫中每個視圖、規(guī)則、默認值、觸發(fā)器、CHECK 約束、DEFAULT 約束和存儲過程的項 SELECT * FROM dbo.syscomments --保存表的自增列信息 SELECT * FROM sys.identity_columns 下面來看看屬性那欄的信息保存在那些表里面。如果表是數(shù)據(jù)庫的默認排序規(guī)則，就可以用下面腳本。 代碼 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' 如果用某個列的排序規(guī)則可用下面的腳本 代碼 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' 查看數(shù)據(jù)庫的排序規(guī)則可以從 sys.databases查看，而表的某個列的排序規(guī)則信息保存在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 如圖所示，得到結(jié)果與文檔還是有些區(qū)別，我通過該腳本實現(xiàn)與文檔一致的時候，怎么也找不到nvarchar(30)的30，這個值的出處，后來才發(fā)現(xiàn)它其實就是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 那么這些權限信息保存在那個系統(tǒng)表或系統(tǒng)視圖中，我查了很多資料，還是沒有查到，呵呵，希望有知道的告訴一聲。但是可以同過系統(tǒng)函數(shù)和系統(tǒng)存儲過程得到一些相關的權限設置信息。 1：系統(tǒng)存儲過程 sp_table_privileges， 它返回指定的一個或多個表的表權限（如 INSERT、DELETE、UPDATE、SELECT、REFERENCES）的列表，表具體參見（MSDN）。 2：系統(tǒng)函數(shù) 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，好像沒有那個系統(tǒng)表、系統(tǒng)視圖保存創(chuàng)建表的腳本（如果有的話，算我孤陋寡聞了），也不能通過SP_HELPTEXT來得到（存儲過程可以），在 網(wǎng)上搜索了下大概有SMO 方式和存儲過程來實現(xiàn)的，SMO方式我還沒來得及驗證，存儲過程倒是找到一個（本來打算自己嘗試下的。呵呵，那這篇文章得耗上好長時間了，等寫完了，自己再寫個試試），下面的存儲過程是我在http://edu.codepub.com/2009/0603/5408.php這里搜索到，也不知道原創(chuàng)作者是誰。 代碼 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 /* 參數(shù)說明： @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='發(fā)現(xiàn)無效的表名: '+@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'沒有可以生產(chǎ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 'Use '+Quotename(db_name())+@Enter+'Go'+@Enter+'/* 創(chuàng)建表結(jié)構 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 該文章在 2011/5/4 15:45:43 編輯過 |
關鍵字查詢
相關文章
正在查詢... 
|
400 186 1886
