Delete a column in all tables using SQL Server 2005

If you are looking out to delete a column across multiple tables, use the query mentioned over here. This query would not work if there are constraints on the column. Just replace 'SomeName' with the column you wish to delete.

DECLARE @TblNames Table
tbl_name nvarchar(100) NULL
DECLARE @str nvarchar(100)
DECLARE @SQL varchar(200);
SET @I = 1
INSERT INTO @TblNames(tbl_name) SELECT OBJECT_NAME(obj.object_id) as [Name]
FROM sys.objects obj inner join sys.columns col
ON col.object_id = obj.object_id
WHERE col.NAME = 'SomeName'
WHILE @I <= @ROWCOUNT BEGIN --SELECT @str = tbl_name FROM @TblNames WHERE COUNTER = @I SET @SQL = 'ALTER TABLE ' + @str + ' DROP COLUMN SomeName' --Print @SQL EXEC (@SQL) SET @I = @I +1 END

About The Author




No comments: