数据库表的基本属性，你知道吗？

关注

原创最新推荐文章于 2026-03-14 12:04:40 发布 9.8k 阅读 AI 写同款· GEO 优化 ›

Azure数据库服务比较：SQL Database vs Cosmos DB 结构化数据场景：如订单管理、用户认证等需要强事务支持的业务非结构化/半结构化场景：如物联网设备日志、社交网络数据、实时推荐系统：基于传统关系型数据库（SQL Server）的云原生服务，支持ACID事务和复杂SQL查询Cosmos DB：全球分布式NoSQL数据库，支持多模型（文档、键值、图、宽列）和弹性扩展两者的架构设计哲学有何本质区别？数据建模和查询语言的差异如何影响开发体验？一致性、可用性、吞吐量的权衡模型如何选择？典型业务场景下的选型决策依据是什么？技术原理对比。阅读详情

用SQL Doc生成数据库字典文档的时候，突然发现有字段描叙(Description)这项内容,以前一直没有注意过，故特意研究了一下,结果越挖越深，就写了这篇文章。

以前在做数据库脚本开发时，新建表时，对各个字段的描叙要么是记录在文档里面，要么自己建一个表，来保存这些内容，以便日后开发、维护的方便。其实这些信息完全可以放在数据库自己的系统视图里面。

对字段的说明、描述一般都放在系统视图sys.extended_properties中，例如(表dbo.Employee的字段Department的说明)

其中当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 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


         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

收录于