数据库表的基本信息，你知道吗？

听溪 2025-04-15

https://www.cnblogs.com/kerrycode/archive/2010/09/03/1816611.html

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

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

对字段的说明、描述一般都放在系统视图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里添加相应的记录。

当然你也可以用脚本命令添加数据库表的字段说明

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这里搜索到，也不知道原创作者是谁。

墨力计划

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

数据库表的基本信息，你知道吗？

评论

相关阅读