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

The above script drops column rowguid from AdventureWorks2014.Person.Addresstype table. Let’s execute the above statement and analyze the result.

1_t-sql script delete column

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.

The output from the above query is shown below.

2_t-sql script delete column

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.

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.

Regards

Ahmad Osama

Like us on FaceBook Join the fastest growing SQL Server group on FaceBook

Follow me on TwitterFollow me on FaceBook