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.

USE [YOURDB]
DECLARE @TblNames Table
(
COUNTER INT IDENTITY(1,1),
tbl_name nvarchar(100) NULL
)
DECLARE @ROWCOUNT INT
DECLARE @I INT
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'
SET @ROWCOUNT = @@ROWCOUNT
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

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: