SELECT * FROM sys.extended_properties
SELECT column_id FROM sys.columns
WHERE object_id = OBJECT_ID ( ' dbo.Employee ' )
AND name = ' Department '
如果已经存在刚才记录,你再执行上面这段脚本,就会提示:
无法添加属性。 ' dbo.Employee.EmployeeName ' 已存在属性 ' MS_Description ' 。
这里先列举一些保存表信息的系统表、视图吧,可能有些遗漏了,实在太多了,要仔细把这些全部列举出来还得花费一番功夫
-- 为每个表和视图中的每列返回一行,并为数据库中的存储过程的每个参数返回一行。
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
modify_date AS Last Modified,
( SELECT collation_name
FROM sys.databases
WHERE name = ' MyAssistant '
) AS collation_name
FROM SYS.tables
WHERE NAME = ' Employee '
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 '
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
修改脚本如下所示
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账号下运行的)
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 ' , @table_owner = ' dbo '
FROM fn_my_permissions( ' dbo.Employee ' , ' OBJECT ' )
ORDER BY subentity_name, permission_name ;
SELECT * FROM fn_my_permissions( ' Kerry ' , ' USER ' );
再来看看SQL Srcipt,好像没有那个系统表、系统视图保存创建表的脚本(如果有的话,算我孤陋寡闻了),也不能通过SP_HELPTEXT来得到(存储过程可以),在
网上搜索了下大概有SMO 方式和存储过程来实现的,SMO方式我还没来得及验证,存储过程倒是找到一个(本来打算自己尝试下的。呵呵,那这篇文章得耗上好长时间了,等写完了,自己再写个试试),下面的存储过程是我在http://edu.codepub.com/2009/0603/5408.php这里搜索到,也不知道原创作者是谁。
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 ' 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