It’s a common task that needs to be done on development/test environment. Though it seems that deleting a column from a table is just running an ALTER TABLE statement, however it’s bit more than this. A T-SQL script to delete column from table is given below
ALTER TABLE Person.Addresstype DROP COLUMN rowguid
The above script drops column rowguid from AdventureWorks2014.Person.Addresstype table. Let’s execute the above statement and analyze the result.
The ALTER TABLE statement fails because other object depends on this column. Thus, before we drop a column we need to find and drop all dependent objects. A dependent object can be a constraint or an index. To find all constraints on a column refer to (link to T-SQL find all constraints on table). To find all indexes on a particular column refer to below query.
SELECT Object_schema_name(objects.object_id, Db_id()) AS SchemaName, objects.NAME AS TableName, indexes.NAME AS IndexName, columns.NAME AS columnname FROM sys.objects objects JOIN sys.indexes indexes ON objects.object_id = indexes.object_id JOIN sys.columns columns ON columns.object_id = objects.object_id JOIN sys.index_columns index_columns ON index_columns.object_id = objects.object_id AND index_columns.index_id = indexes.index_id AND index_columns.column_id = columns.column_id WHERE objects.schema_id = Schema_id( Object_schema_name(objects.object_id, Db_id())) AND objects.NAME = 'AddressType'
The output from the above query is shown below.
Now, drop the dependent constraints and indexes and then drop the column. Another thing to check is that the column exists in table or not.
IF EXISTS(SELECT 1 FROM sys.objects objects JOIN sys.columns columns ON objects.object_id = columns.object_id WHERE objects.object_id = Object_id('Person.AddressType') AND columns.NAME = 'rowguid') BEGIN ALTER TABLE person.addresstype DROP COLUMN rowguid END
One last thing is to check what all objects (procedure/functions/views/triggers…) refer to the column. Refer to this LINK to find objects referring to a column. The link lists down stored procedure related to column, however the query can be easily modified to find other objects.