【Sqlserver】字段编辑

删除字段

USE HotelQuality;
IF NOT EXISTS ( SELECT  Name
              FROM    sysColumns	
           WHERE   ID = OBJECT_ID('[dbo].[NC_FolderInFo]')
                        AND Name = 'JoinHotelParentId' )
BEGIN
	alter table dbo.NC_FolderInFo drop constraint [DF__NC_Folder__JoinH__377B294A];
    alter table dbo.NC_FolderInFo drop column  JoinHotelParentId
END

添加字段

USE HotelQuality;
IF NOT EXISTS ( SELECT  Name
                FROM    sysColumns
                WHERE   ID = OBJECT_ID('[dbo].[VRHotel]')
                        AND Name = 'StoreManager' )
BEGIN
    ALTER TABLE dbo.VRHotel
    ADD [StoreManager] int NOT NULL DEFAULT 0
END

注释添加

添加注释主要使用到 sys.sp_addextendedpropert 存储过程。对应更新和删除为：sp_updateextendedproperty、sp_dropextendedproperty 官网链接：https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-dropextendedproperty-transact-sql

1、给表添加注释

EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', @value=N'注释' , 	
@level0type=N'SCHEMA',@level0name=N'dbo', 
@level1type=N'TABLE',@level1name=N'表名', '

2、给字段添加注释 

EXEC sys.sp_addextendedproperty 
@name=N'MS_Description', @value=N'注释' , 	
@level0type=N'SCHEMA',@level0name=N'dbo', 
@level1type=N'TABLE',@level1name=N'表名', 
@level2type=N'COLUMN',@level2name=N'字段名';

3、查看注释信息

-- 查看当前数据库中的table
select a.object_id,a.* from sys.tables a where name = 'user';
-- 查看表的字段信息
select * from sys.columns a where a.object_id = 293576084;
-- 扩展属性表,当class 为1对象列 2参数 7索引 是major为object_id,minor为字段id
select * from  sys.extended_properties a where a.major_id =293576084;