MSSQL创建表并添加表注释及字段注释

2021-11-20 05:09:14

创建表

create table testTable (
id varchar(200) primary key not null,
testRecord varchar(200),
)

为字段添加注释

格式如右：execute sp_addextendedproperty ‘MS_Description‘,‘字段备注信息‘,‘user‘,‘dbo‘,‘table‘,‘字段所属的表名‘,‘column‘,‘添加注释的字段名‘;
execute sp_addextendedproperty ‘MS_Description‘,‘添加的注释内容‘,‘user‘,‘dbo‘,‘table‘,‘testTable‘,‘column‘,‘testRecord‘;

修改字段注释

execute sp_updateextendedproperty ‘MS_Description‘,

‘修改的注释内容‘,‘user‘,‘dbo‘,‘table‘,‘testTable‘,‘column‘,‘testRecord‘;

删除字段注释

execute sp_dropextendedproperty ‘MS_Description‘,‘user‘,‘dbo‘,‘table‘,‘testTable‘,‘column‘,‘testRecord‘;

添加表注释

execute sp_addextendedproperty ‘MS_Description‘,‘注释-些表为测试表‘,‘user‘,‘dbo‘,‘table‘,‘testTable‘,null,null;

修改表注释

execute sp_updateextendedproperty ‘MS_Description‘,‘修改注释-测试表‘,‘user‘,‘dbo‘,‘table‘,‘testTable‘,null,null;

删除表注释

execute sp_dropextendedproperty ‘MS_Description‘,‘user‘,‘dbo‘,‘table‘,‘testTable‘,null,null;

获取表，字段的注释

select
[表名]=c.Name,
[表说明]=isnull(f.[value],‘‘),
[列名]=a.Name,
[列序号]=a.Column_id,
[标识]=case when is_identity=1 then ‘√‘ else ‘‘ end,
[主键]=case when exists(select 1 from sys.objects where parent_object_id=a.object_id and type=N‘PK‘ and name in
(select Name from sys.indexes where index_id in
(select indid from sysindexkeys where  colid=a.column_id)))
then ‘√‘ else ‘‘ end,
[类型]=b.Name,
[字节数]=case when a.[max_length]=-1 and b.Name!=‘xml‘ then ‘max/2G‘
when b.Name=‘xml‘ then ‘ 2^31-1字节/2G‘
else rtrim(a.[max_length]) end,
[长度]=ColumnProperty(a.object_id,a.Name,‘Precision‘),
[小数]=isnull(ColumnProperty(a.object_id,a.Name,‘Scale‘),0),
[是否為空]=case when a.is_nullable=1 then ‘√‘ else ‘‘ end,
[列说明]=isnull(e.[value],‘‘),
[默认值]=isnull(d.text,‘‘)
from
sys.columns a
left join
sys.types b on a.user_type_id=b.user_type_id
inner join
sys.objects c on a.object_id=c.object_id and c.Type=‘U‘
left join
syscomments d on a.default_object_id=d.ID
left join
sys.extended_properties e on e.major_id=c.object_id and e.minor_id=a.Column_id and e.class=1
left join
sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1
--表名字
where c.Name = ‘table‘




SELECT
(case when a.colorder=1 then d.name else ‘‘ end) [数据表名称],
a.colorder [列顺序],
a.name [列名称],
b.name as [列类型],
b.length as [列长度],
g.[value] AS [列说明信息]
FROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype=‘U‘ and d.name<>‘dtproperties‘
left join sys.extended_properties g
on a.id=g.major_id AND a.colid = g.minor_id 
--WHERE d.[name] =‘table‘--数据表名称
order by a.id,a.colorder