T-SQL script delete column from a table

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.

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