我需要在SQL Server 2008中创建一些相当大的表格。虽然我有SQL Server Management Studio (SSMS)，但我想在创建表格和列时添加注释。我该怎么做？
我要运行的查询示例：
CREATE TABLE cert_Certifications
(
certificationID int PRIMARY KEY IDENTITY,
profileID int,
cprAdultExp datetime null
)
我尝试在数据类型后面添加COMMENT'成人CPR的有效期'和COMMENT ='成人CPR的有效期'，但SQL Server报错了。
这是我使用的内容
/*==============================================================*/
/* 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
您可以通过创建所谓的扩展属性在表格和列上添加注释。您可以在表格级别和列级别上设置扩展属性。这可以通过T-SQL或SSMS完成。
例如，在T-SQL中，它看起来像这样：
sp_addextendedproperty 'BackColor', 'Red', 'user', '<schema name>', 'table', '<table name', 'column', '<column name>'.
这篇文章中有好的答案。补充一点，'MS_Description' 的值也可以是其他内容。例如，我们可以使用'SourceDescription'来描述数据源的详细信息，'TableDescription'用于表格，'ColumnDescription'用于每个表格列。
示例：
-- 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'
我喜欢在设计表格时使用图形用户界面(GUI)，因为这样我可以更好地可视化布局。在GUI设计器中，可以在属性窗口中为表格和列添加描述，如下图所示：
-- 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
@name = ''ColumnDescription'',@level0type = ''SCHEMA'',
'@level1type= N''TABLE'', @level2type= N''COLUMN'', @level0name=N''' + TABLE_SCHEMA + ''', @level1name=N''subacct'', @level2name= N''' + COLUMN_NAME + ''', @value` = ''TODO'';'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA not like 'sys%' and TABLE_NAME ='subacct'
order by TABLE_SCHEMA, TABLE_NAME, case when ORDINAL_POSITION = 1 then '0' else COLUMN_NAME end - gg89创建表 TABLE NAME (ATTRIBUTE NAME (ATTRIBUTE SIZE)) // create和table都是关键字