字段/表名添加表备注

字段/表名修改

查询表备注

SELECT 
	TableName=O.name, 
	TableDesc=PTB.[value]
	FROM sys.columns C with(nolock)
	INNER JOIN sys.objects O with(nolock)
	ON C.[object_id]=O.[object_id] 
	AND O.type='U' 
	AND O.is_ms_shipped=0 
	and c.column_id=1
	LEFT JOIN sys.extended_properties PTB with(nolock)
	ON PTB.class=1 
	AND PTB.minor_id=0 
	AND C.[object_id]=PTB.major_id 
	INNER JOIN sys.types T 
	ON C.user_type_id=T.user_type_id and t.name!='sysname'

增加列
alter table Direct_SecKill_Info add InnerProductId int

修改列类型
alter table Direct_SecKill_Info alter column InnerProductId  varchar(10)

修改表名
exec sp_rename 'tableName',  'newTableName'

修改字段名
alter table tableName rename column a to c 

Sql Server 获取、增加、修改、删除 字段描述  删除列、列约束

获取、增加、修改、删除sqlserver字段描述

 
Select o.name AS tableName, c.name AS columnName, p.[value] AS Description   
FROM sysproperties p INNER JOIN   
      sysobjects o ON o.id = p.id INNER JOIN   
      syscolumns c ON p.id = c.id AND p.smallid = c.colid   
Where (p.name = 'MS_Description') AND (c.name = 'province') AND (o.name = 'ip_address')   
orDER BY o.name   
  
选取表ip_address中 字段province的描述   
  
 
--创建表及描述信息   
 
--为表添加描述信息   
EXECUTE   sp_addextendedproperty   N'MS_Description',   '人员信息表',   N'user',   N'dbo',   N'table',   N'表',   NULL,   NULL   
  
--为字段a1添加描述信息   
EXECUTE   sp_addextendedproperty   N'MS_Description',   '姓名',   N'user',   N'dbo',   N'table',   N'表',   N'column',   N'a1' 

EXECUTE   sp_addextendedproperty   N'MS_Description',   '测试',   N'user',   N'dbo',   N'table',   N'HR_Employees',   N'column',   N'test' 
  
--为字段a2添加描述信息   
EXECUTE   sp_addextendedproperty   N'MS_Description',   '性别',   N'user',   N'dbo',   N'table',   N'表',   N'column',   N'a2'  

--更新表中列a1的描述属性：   
EXECUTE   sp_updateextendedproperty   'MS_Description','字段1','user',dbo,'table',N'表','column',N'a1'   
  
--删除表中列a1的描述属性：   
EXECUTE   sp_dropextendedproperty   'MS_Description','user',dbo,'table',N'表','column',N'a1'  

--删除约束
ALTER TABLE tabeName  Drop CONSTRAINT 约束名
--删除列
  ALTER TABLE  tabeName DROP COLUMN 列名