DECLARE @s NVARCHAR(max) SET @s='' SELECT @s=@s+' alter table ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] drop column ID;' FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME='ID' AND objectproperty(OBJECT_ID(Table_Name),'IsUserTable')=1EXEC(@s)以字段ID為例,要看這個字段有沒有被引用,被引用時刪不了
EXEC sp_MSforeachtable @command1="if exists(Select 1 From syscolumns where id=object_id('?') and name ='ACC') alter table ? drop column Acc" --如果有外键不知道会怎么样哦 --ACC替换成你要删除的字段
配合sqlserver的系统表可以做到,这风险也恁大的吧
declare @Sql nvarchar(4000) select @Sql=isnull(@Sql,'')+'ALTER table '+t.name+' DROP COLUMN upsize_ts; ' from (select distinct v1.name from sys.objects v1 inner join sys .columns v2 on v1.object_id=v2.object_id where v1.type='U' and v2.name='upsize_ts') t print(@Sql) exec(@Sql)
SET @s=''
SELECT @s=@s+' alter table ['+TABLE_SCHEMA+'].['+TABLE_NAME+'] drop column ID;'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME='ID' AND objectproperty(OBJECT_ID(Table_Name),'IsUserTable')=1EXEC(@s)以字段ID為例,要看這個字段有沒有被引用,被引用時刪不了
--如果有外键不知道会怎么样哦
--ACC替换成你要删除的字段
declare @Sql nvarchar(4000)
select @Sql=isnull(@Sql,'')+'ALTER table '+t.name+' DROP COLUMN upsize_ts; ' from (select distinct v1.name from sys.objects v1 inner join sys .columns v2 on v1.object_id=v2.object_id where v1.type='U' and v2.name='upsize_ts') t
print(@Sql)
exec(@Sql)