Search sqlserver-dba.com
Follow sqlserver-dba.com
For any SQL Server issue send email to: jack@sqlserver-dba.com
SQLServer-DBA.com Links
Recent Posts
- How to fix Msg 7390 - The requested operation could be performed
- How to find physical path for SQL Server backup on Container
- How to fix Msg 7347 - linked server returned data that does not match expected data length for column
- How to check SQL Server Filegroup file sizes
- How to check if SQL Server Agent is running using t-sql
- How to check if Puppet is reboots a windows server with Powershell
- How To Use Transparent Data Encryption To Increase Your Security
- Quick Fix: How to display hours minutes seconds between two dates
- Use Get-ADPrincipalGroupMembership with other Active Directory domains
- On-Demand Instant File Initialization status with sys.dm_server_services
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
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.
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: |