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

19 May,2014 by Jack Vamvas

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?

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

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


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer