数据库表的基本属性，你知道吗？
⽤SQL Doc⽣成数据库字典⽂档的时候，突然发现有字段描叙(Description)这项内容，以前⼀直没有注意过，故特意研究了⼀下，结果越挖越深，就写了这篇⽂章。
以前在做数据库脚本开发时，新建表时，对各个字段的描叙要么是记录在⽂档⾥⾯，要么⾃⼰建⼀个表，来保存这些内容，以便⽇后开发、维护的⽅便。其实这些信息完全可以放在数据库⾃⼰的系统视图⾥⾯。
对字段的说明、描述⼀般都放在系统视图ded_properties中，例如(表dbo.Employee的字段Department的说明)
，如下图所⽰
其中当class =1时，major_id它的值是dbo.Employee的id，minor_id是Department的id(详细信息参见MSDN)
SELECT  column_id FROM    lumns
WHERE  object_id = OBJECT_ID('dbo.Employee')
AND name = 'Department'
其实在MSSMS 管理器中，选中要添加字段说明的表，单击右键——》修改（08是设计），如下图所⽰，增加后，保存。就会在ded_properties
⾥添加相应的记录。Array当然你也可以⽤脚本命令添加数据库表的字段说明
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 * lumns
--为每个表和视图中的每列返回⼀⾏，并为数据库中的存储过程的每个参数返回⼀⾏。
SELECT * FROM syscolumns
--每个表对象的信息
SELECT * FROM sys.tables
SELECT * FROM sysobjects
--在数据库中创建的每个⽤户定义的架构范围内的对象的信息
SELECT * FROM sys.objects
--数据库实例中的每个数据库的信息
SELECT * FROM sys.databases
--系统数据类型
SELECT * pes
--含数据库中每个视图、规则、默认值、触发器、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,
, '')  AS  DefaultValue,
ISNULL(P.value, '') AS FieldComment
lumns  C
INNER JOIN  pes T ON C.system_type_id = T.user_type_id
LEFT JOIN dbo.syscomments M ON M.id = C.default_object_id
LEFT  ded_properties P ON P.major_id = C.object_id lumn_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,
, '')  AS  DefaultValue,
ISNULL(P.value, '') AS FieldComment
lumns  C
INNER JOIN  pes T ON C.system_type_id = T.user_type_id
数据库属性的概念LEFT  JOIN  dbo.syscomments M ON M.id = C.default_object_id
LEFT  JOIN  ded_properties P ON P.major_id = C.object_id lumn_id = P.minor_id
LEFT  JOIN  sys.identity_columns I lumn_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来得到（存储过程可以），在
代码
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
ferenced_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_ferenced_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 ded_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

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。