Search code examples
sql-server-2008-r2alter-tablealter

Drop column from multiple tables in particular schema in SQL Server

I have many table containing column as "RowId" in different Schema like dbo, stg, tmp etc but i want to drop column of only stg schema tables.

please give a script to followed above condition.

Thanx in advance

Solution

  • back up first

    DECLARE @SqlStatement VARCHAR(MAX)
SELECT @SqlStatement = 
    COALESCE(@SqlStatement, '') + 'ALTER TABLE [stg].' 
    + QUOTENAME(TABLE_NAME) + ' DROP COLUMN [RowId];' + CHAR(13)
FROM INFORMATION_SCHEMA.TABLES t 
  INNER JOIN sys.objects o ON o.name=t.TABLE_NAME
    INNER JOIN sys.columns c ON  o.object_id= c.object_id
WHERE TABLE_SCHEMA = 'stg' and  c.name='RowId'

PRINT @SqlStatement

    Once you are shure, execute dinamyc SQL Statement

    exec sp_executesql @SqlStatement