How to drop a SQL column and find foreign keys with sp_fkeys

Question: What is the syntax  for dropping a SQL table column? What are some considerations in dropping a column?

Answer: Dropping a SQL column is straightforward, but before you execute the code, think about the impact on data integrity and application errors which may occur.

1)      If other tables reference this column, you’ll need to decide on what to do with the other table columns. One approach is to remove the foreign keys and maintain the data in the referencing tables.  Update your documentation to explain the purpose of this change

2)      A second option is to identify the referencing columns and remove , if they are not used anymore by queries

3)      The most work involved with dropping a column is identifying the foreign keys. There are a number of commercial products available which track dependencies . You can spin up your own DIY solution, which may require Search for text in stored procedures and application code.

4)      A useful stored procedure is  EXEC sp_fkeys 'TableName'.  Sp_fkeys  reports on logical foreign key details. Check SQL Server BOL for more details

Syntax to drop a SQL column.  Make sure to backup data before dropping any columns!

ALTER TABLE myTable DROP COLUMN myColumn

Read More

JOIN IN SQL FAQ

SQL server – What is a SQL index key?