19 May,2014 by Tom Collins
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
SQL server – What is a SQL index key?
This is only a preview. Your comment has not yet been posted.
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.
Posted by: |