在 SQL Server 2008 上创建表的 SQL 注释
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4586842/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Comments on Create Table on SQL Server 2008
提问by Snow_Mac
I need to create some pretty big tables in SQL Server 2008, while I do have SQL Server Management Studio, I would like to comment the tables and the columns when I create the table. How do I do this?
我需要在 SQL Server 2008 中创建一些非常大的表，虽然我有 SQL Server Management Studio，但我想在创建表时注释表和列。我该怎么做呢？
Example of the query I am running:
我正在运行的查询示例：
CREATE TABLE cert_Certifications
(
certificationID int PRIMARY KEY IDENTITY,
profileID int,
cprAdultExp datetime null
)
I've tried COMMENT'Expiration Date for the Adult CPR' and COMMENT='Expiration Date for the Adult CPR' after the data type, and SQL Server is giving me an error.
我在数据类型之后尝试了 COMMENT'Expiration Date for the Adult CPR' 和 COMMENT='Expiration Date for the Adult CPR'，SQL Server 给了我一个错误。
采纳答案by Randy Minder
You can put comments on both tables and columns by creating what are called Extended Properties. You can put extended properties at both the table level and column level. This can be done via T-SQL or SSMS.
您可以通过创建所谓的扩展属性对表和列进行注释。您可以在表级别和列级别放置扩展属性。这可以通过 T-SQL 或 SSMS 来完成。
For example, in T-SQL it looks something like this:
例如，在 T-SQL 中，它看起来像这样：
sp_addextendedproperty 'BackColor', 'Red', 'user', '<schema name>', 'table', '<table name', 'column', '<column name>'.
You can read more about it here
回答by J Henzel
This is what I use
这是我使用的
/*==============================================================*/
/* Table: TABLE_1 */
/*==============================================================*/
create table TABLE_1 (
ID int identity,
COLUMN_1 varchar(10) null,
COLUMN_2 varchar(10) null,
constraint PK_TABLE_1 primary key nonclustered (ID)
)
go
declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description',
'This is my table comment',
'user', @CurrentUser, 'table', 'TABLE_1'
go
declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description',
'This is the primary key comment',
'user', @CurrentUser, 'table', 'TABLE_1', 'column', 'ID'
go
declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description',
'This is column one comment',
'user', @CurrentUser, 'table', 'TABLE_1', 'column', 'COLUMN_1'
go
declare @CurrentUser sysname
select @CurrentUser = user_name()
execute sp_addextendedproperty 'MS_Description',
'This is column 2 comment',
'user', @CurrentUser, 'table', 'TABLE_1', 'column', 'COLUMN_2'
go
回答by RC_Cleland
I prefer the GUI when desinging tables because I can visualize the layout better. In the GUI designer one can add a description for the table and columns in the properties window as shown in the image below
我在设计表格时更喜欢 GUI，因为我可以更好地可视化布局。在 GUI 设计器中，可以在属性窗口中添加表和列的描述，如下图所示
回答by Max Duarte
There are good answers in this post. Adding that the value 'MS_Description' could be other thing. For example, we can use 'SourceDescription' for details about the source the data, 'TableDescription' for table and 'ColumnDescription' for each column on table.
这篇文章中有很好的答案。添加值“MS_Description”可能是另一回事。例如，我们可以使用“SourceDescription”获取有关数据源的详细信息，“TableDescription”用于表，“ColumnDescription”用于表中的每一列。
Example:
例子：
-- Create example table
create table testTablename(
id int,
name varchar(20),
registerNumber bigint
)
-- SourceDescription
EXEC sys.sp_addextendedproperty
@name=N'SourceDescription',
@value=N'Result of process x union y ' , -- Comment about the source this data.
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'testTableName' -- Name of Table
-- TableDescription
EXEC sys.sp_addextendedproperty
@name=N'TableDescription',
@value=N'Table is used for send email to clients.' , -- Coment about the used of table
@level0type=N'SCHEMA',
@level0name=N'dbo',
@level1type=N'TABLE',
@level1name=N'testTableName'
-- ColumnDescription
EXECUTE sp_addextendedproperty
@name = 'ColumnDescription',
@value = 'Unique identification of employer. Its the registry of company too.',
@level0type = 'SCHEMA',
@level0name= N'dbo',
@level1type = N'TABLE',
@level1name = N'testTableName',
@level2type = N'COLUMN',
@level2name = N'registerNumber'
-- If necessary, you can delete the comment.
exec sp_dropextendedproperty
@name = 'ColumnDescription',
@level0type = 'SCHEMA',
@level0name= N'dbo',
@level1type = N'TABLE',
@level1name = N'testTableName',
@level2type = N'COLUMN',
@level2name = N'registerNumber'
-- Show you the table resume
select
tables.name tableName,
tables.create_date,
tables.modify_date,
tableDesc.value TableDescription,
sourceDesc.value SourceDescription
from
sys.tables
left join sys.extended_properties tableDesc on tables.object_id = tableDesc.major_id and tableDesc.name = 'TableDescription'
left join sys.extended_properties sourceDesc on tables.object_id = sourceDesc.major_id and sourceDesc.name = 'SourceDescription'
where
tableDesc.name in('TableDescription', 'SourceDescription', 'ColumnDescription')
order by tables.name
-- show you the columns resume
select
tables.name tableName,
columns.name columnName,
extended_properties.value
from
sys.tables
inner join sys.columns on tables.object_id = columns.object_id
left join sys.extended_properties on
tables.object_id = extended_properties.major_id
and columns.column_id = extended_properties.minor_id
and extended_properties.name in('MS_Description','ColumnDescription')
where
tables.name = 'testTableName'
回答by Klaus Byskov Pedersen
You need to use the stored procedure called sp_addextendedpropertyto add comments to columns/tables in Sql Server.
您需要使用名为sp_addextendedproperty的存储过程向 Sql Server 中的列/表添加注释。
回答by AFract
Altough it does not directly answer original question (J Henzel and Randy Minder already did !) I would like to share something else I just wrote that can be very useful for those who have to comment a lot of tables and columns.
虽然它没有直接回答原始问题（J Henzel 和 Randy Minder 已经回答了！）我想分享我刚刚写的其他一些东西，这些东西对于那些必须评论大量表格和列的人来说非常有用。
The following queries :
以下查询：
-- Generate comments templates for all tables
SELECT
'EXEC sys.sp_addextendedproperty
@name=N''TableDescription'',
@level0type=N''SCHEMA'',
@level1type=N''TABLE'',
@level0name=N''' + TABLE_SCHEMA + ''',
@level1name=N''' + TABLE_NAME + ''',
@value=N''TODO'';'
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME NOT like 'sys%'
order by TABLE_SCHEMA, TABLE_NAME
-- Generate comments templates for all columns
SELECT 'EXECUTE sp_addextendedproperty
@name = ''ColumnDescription'',
@level0type = ''SCHEMA'',
@level1type = N''TABLE'',
@level2type = N''COLUMN'',
@level0name=N''' + TABLE_SCHEMA + ''',
@level1name=N''' + TABLE_NAME + ''',
@level2name = N''' + COLUMN_NAME + ''',
@value = ''TODO'';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA not like 'sys%' and TABLE_NAME not like 'sysdiagrams%'
order by TABLE_SCHEMA, TABLE_NAME, case when ORDINAL_POSITION = 1 then '0' else COLUMN_NAME end
Will produce in SQL Server output a list of calls to sp_addextendedproperty for all the tables and all the columns existing in your database, by querying on system tables to gather them.
通过查询系统表来收集它们，将在 SQL Server 输出中为所有表和数据库中存在的所有列生成对 sp_addextendedproperty 的调用列表。
Of course, it will not comment it magically for you, but at least you just have to fill the "TODOs" placeholders with the relevant comment for all objects you would like to describe and to execute it.
当然，它不会为你神奇地评论它，但至少你只需要用你想要描述的所有对象的相关评论填充“待办事项”占位符并执行它。
It avoids you to write manually all the calls and saves a lot of time, and with it you can't forget a table or column so I hope it will be useful for somebody else.
它避免了您手动编写所有调用并节省了大量时间，并且使用它您不会忘记一个表或列，所以我希望它对其他人有用。
Side remarks : Just beware on the filters in WHEREs on "sys", it's here to exclude system objects but depending of your objects names you may need a bit of fine tuning of you have tables named alike.
旁注：请注意“sys”上 WHEREs 中的过滤器，它在这里排除系统对象，但根据您的对象名称，您可能需要对具有相似名称的表进行一些微调。
Also, there's no comment at all in my DB so my query returns all tables/columns, it does not consider wether there's already a comment or not on it.
此外，我的数据库中根本没有评论，所以我的查询返回所有表/列，它不考虑是否已经有评论。
回答by Dasanan
use this sql command
使用这个 sql 命令
Create table TABLE NAME (ATTRIBUTE NAME (ATTRIBUTE SIZE))// both createand tableare Keywords
Create table TABLE NAME (ATTRIBUTE NAME (ATTRIBUTE SIZE))//create和table都是关键字
