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

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook