建站
咨詢
售后
建站咨詢
新聞中心
用SQL Doc生成數(shù)據(jù)庫(kù)字典文檔的時(shí)候，突然發(fā)現(xiàn)有字段描敘(Description)這項(xiàng)內(nèi)容,以前一直沒(méi)有注意過(guò)，故特意研究了一下,結(jié)果越挖越深，就寫(xiě)了這篇文章。
成都創(chuàng)新互聯(lián)公司主要從事網(wǎng)站制作、成都網(wǎng)站設(shè)計(jì)、網(wǎng)頁(yè)設(shè)計(jì)、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務(wù)。立足成都服務(wù)嘉祥,10多年網(wǎng)站建設(shè)經(jīng)驗(yàn),價(jià)格優(yōu)惠、服務(wù)專(zhuān)業(yè),歡迎來(lái)電咨詢建站服務(wù):13518219792
以前在做數(shù)據(jù)庫(kù)腳本開(kāi)發(fā)時(shí)，新建表時(shí)，對(duì)各個(gè)字段的描敘要么是記錄在文檔里面，要么自己建一個(gè)表，來(lái)保存這些內(nèi)容，以便日后開(kāi)發(fā)、維護(hù)的方便。其實(shí)這些信息完全可以放在數(shù)據(jù)庫(kù)自己的系統(tǒng)視圖里面。
對(duì)字段的說(shuō)明、描述一般都放在系統(tǒng)視圖sys.extended_properties中，例如(表dbo.Employee的字段Department的說(shuō)明
- SELECT * FROM dbo.Employee
- SELECT * FROM sys.extended_properties
其中 當(dāng)class =1時(shí)，major_id它的值是dbo.Employee的id，minor_id是Department的id(詳細(xì)信息參見(jiàn)MSDN)，如下圖所示
- SELECT OBJECT_ID(N'dbo.Employee')
- SELECT column_id FROM sys.columns
- WHERE object_id = OBJECT_ID('dbo.Employee')
- AND name = 'Department'
其實(shí)在MSSMS 管理器中，選中要添加字段說(shuō)明的表，單擊右鍵——》修改（08是設(shè)計(jì)），如下圖所示，增加后，保存。就會(huì)在sys.extended_properties里添加相應(yīng)的記錄。
當(dāng)然你也可以用腳本命令添加數(shù)據(jù)庫(kù)表的字段說(shuō)明
- EXEC sp_addextendedproperty N'MS_Description', N'雇員名稱(chēng)', 'SCHEMA', N'dbo', 'TABLE', N'Employee', 'COLUMN', N'EmployeeName'
如果已經(jīng)存在剛才記錄，你再執(zhí)行上面這段腳本，就會(huì)提示：
消息 15233，級(jí)別 16，狀態(tài) 1，過(guò)程 sp_addextendedproperty，第 38 行
無(wú)法添加屬性。'dbo.Employee.EmployeeName' 已存在屬性 'MS_Description'。
下面看看工具生成的文檔，工具生成這些信息肯定是數(shù)據(jù)庫(kù)里存有對(duì)象的這些信息，下面我們來(lái)看看這些信息都是從何而來(lái)吧
這里先列舉一些保存表信息的系統(tǒng)表、視圖吧，可能有些遺漏了，實(shí)在太多了，要仔細(xì)把這些全部列舉出來(lái)還得花費(fèi)一番功夫
- SELECT * FROM sys.columns
- --為每個(gè)表和視圖中的每列返回一行，并為數(shù)據(jù)庫(kù)中的存儲(chǔ)過(guò)程的每個(gè)參數(shù)返回一行。
- SELECT * FROM syscolumns
- --每個(gè)表對(duì)象的信息
- SELECT * FROM sys.tables
- SELECT * FROM sysobjects
- --在數(shù)據(jù)庫(kù)中創(chuàng)建的每個(gè)用戶定義的架構(gòu)范圍內(nèi)的對(duì)象的信息
- SELECT * FROM sys.objects
- --數(shù)據(jù)庫(kù)實(shí)例中的每個(gè)數(shù)據(jù)庫(kù)的信息
- SELECT * FROM sys.databases
- --系統(tǒng)數(shù)據(jù)類(lèi)型
- SELECT * FROM sys.types
- --含數(shù)據(jù)庫(kù)中每個(gè)視圖、規(guī)則、默認(rèn)值、觸發(fā)器、CHECK 約束、DEFAULT 約束和存儲(chǔ)過(guò)程的項(xiàng)
- SELECT * FROM dbo.syscomments
- --保存表的自增列信息
- SELECT * FROM sys.identity_columns
下面來(lái)看看屬性那欄的信息保存在那些表里面。如果表是數(shù)據(jù)庫(kù)的默認(rèn)排序規(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'
如果用某個(gè)列的排序規(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ù)庫(kù)的排序規(guī)則可以從 sys.databases查看，而表的某個(gè)列的排序規(guī)則信息保存在syscolumns里面。上圖的Heap， Row Count信息我還不知是從哪里來(lái)的。
接下來(lái)看看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ū)別，我通過(guò)該腳本實(shí)現(xiàn)與文檔一致的時(shí)候，怎么也找不到nvarchar(30)的30，這個(gè)值的出處，后來(lái)才發(fā)現(xiàn)它其實(shí)就是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
接下來(lái)看看Perssion信息來(lái)自何處。 首先我們來(lái)看看賦與、收回權(quán)限的腳本（我是在sa賬號(hào)下運(yùn)行的）
- 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
那么這些權(quán)限信息保存在那個(gè)系統(tǒng)表或系統(tǒng)視圖中，我查了很多資料，還是沒(méi)有查到，呵呵，希望有知道的告訴一聲。但是可以同過(guò)系統(tǒng)函數(shù)和系統(tǒng)存儲(chǔ)過(guò)程得到一些相關(guān)的權(quán)限設(shè)置信息。
1：系統(tǒng)存儲(chǔ)過(guò)程 sp_table_privileges， 它返回指定的一個(gè)或多個(gè)表的表權(quán)限（如 INSERT、DELETE、UPDATE、SELECT、REFERENCES）的列表，表具體參見(jiàn)（MSDN）。
2：系統(tǒng)函數(shù) fn_my_permissions 返回有效授予主體對(duì)安全對(duì)象的權(quán)限的列表，表具體參見(jiàn)（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的有效權(quán)限
- SELECT * FROM fn_my_permissions('Kerry', 'USER');
再來(lái)看看SQL Srcipt，好像沒(méi)有那個(gè)系統(tǒng)表、系統(tǒng)視圖保存創(chuàng)建表的腳本（如果有的話，算我孤陋寡聞了），也不能通過(guò)SP_HELPTEXT來(lái)得到（存儲(chǔ)過(guò)程可以），在
網(wǎng)上搜索了下大概有SMO 方式和存儲(chǔ)過(guò)程來(lái)實(shí)現(xiàn)的，SMO方式我還沒(méi)來(lái)得及驗(yàn)證，存儲(chǔ)過(guò)程倒是找到一個(gè)（本來(lái)打算自己嘗試下的。呵呵，那這篇文章得耗上好長(zhǎng)時(shí)間了，等寫(xiě)完了，自己再寫(xiě)個(gè)試試），下面的存儲(chǔ)過(guò)程是我在http://edu.codepub.com/2009/0603/5408.php這里搜索到，也不知道原創(chuàng)作者是誰(shuí)。
- 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ù)說(shuō)明：
- @objectList 對(duì)象列表，對(duì)象之間使用","隔開(kāi)
-
- 改存儲(chǔ)過(guò)程生成的建表腳本，包含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)無(wú)效的表名: '+@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'沒(méi)有可以生產(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 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
- &nb
文章題目：數(shù)據(jù)庫(kù)表的基本信息，你真的都了解嗎？
網(wǎng)頁(yè)URL：http://www.dlmjj.cn/article/ccecssj.html
