需要用到游标来遍历整个表下面是批量删除一个表的多个字段的declare @tablename varchar(30) DECLARE cur_temp Cursor For select b.name from dbo.syscolumns a,dbo.sysobjects b where a.name = colname --要删的字段 and a.id = b.id and b.type = 'U' OPEN cur_temp_memid FETCH cur_tempd Into @tablename while @@fetch_status = 0 begin exec('alter table '+@tablename+' drop column colname') FETCH cur_temp Into @tablename endClose cur_temp Deallocate cur_temp
EXEC sp_dropextendedproperty N'MS_Description',@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'表名'--实例:
EXEC sp_dropextendedproperty N'MS_Description', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'XmlQuery'--删除字段
ALTER TABLE ROYAL_DTABLE DROP COLUMN RM_INT_FIELD
--删除表说明--语法:
EXEC sp_dropextendedproperty N'MS_Description',@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'表名'--实例:
EXEC sp_dropextendedproperty N'MS_Description', @level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'XmlQuery'--删除字段
ALTER TABLE ROYAL_DTABLE DROP COLUMN RM_INT_FIELD
--删除字段说明
--语法:
EXEC sp_dropextendedproperty 'MS_Description',@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'表名', @level2type=N'COLUMN', @level2name=N'字段名'--实例:
EXEC sp_dropextendedproperty 'MS_Description',@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'XmlQuery', @level2type=N'COLUMN', @level2name=N'Name'
语法:EXEC sp_updateextendedproperty 'MS_Description',@value=N'字段说明',@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'表名', @level2type=N'COLUMN', @level2name=N'字段名'实例:EXEC sp_updateextendedproperty 'MS_Description', @value=N'是否为大额签报(0:否;1:是)' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'Quota_Report', @level2type=N'COLUMN', @level2name=N'IsMaxReport'6、删除表字段说明语法:EXEC sp_dropextendedproperty 'MS_Description',@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'表名', @level2type=N'COLUMN', @level2name=N'字段名'实例:EXEC sp_dropextendedproperty 'MS_Description',@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'TABLE', @level1name=N'XmlQuery', @level2type=N'COLUMN', @level2name=N'Name'
DECLARE cur_temp Cursor For
select b.name from dbo.syscolumns a,dbo.sysobjects b
where a.name = colname --要删的字段
and a.id = b.id
and b.type = 'U'
OPEN cur_temp_memid
FETCH cur_tempd Into @tablename
while @@fetch_status = 0
begin
exec('alter table '+@tablename+' drop column colname') FETCH cur_temp Into @tablename
endClose cur_temp
Deallocate cur_temp