[sql-server] How to remove a column from an existing table?

How to remove a column from an existing table?

I have a table MEN with Fname and Lname

I need to remove the Lname

How to do it?

Syntax:

ALTER TABLE TABLE_NAME DROP COLUMN COLUMN_NAME;

For Example:

alter table Employee drop column address;

The question is, can you only delete a column from an unexisting table ;-)

BEGIN TRANSACTION

IF exists (SELECT * FROM  sys.columns c 
INNER JOIN  sys.objects t ON (c.[object_id] = t.[object_id])
WHERE t.[object_id] = OBJECT_ID(N'[dbo].[MyTable]')
AND c.[name] = 'ColumnName')
    BEGIN TRY
        ALTER TABLE [dbo].[MyTable] DROP COLUMN ColumnName
    END TRY
    BEGIN CATCH
        print 'FAILED!'
    END CATCH
ELSE
    BEGIN 
        SELECT ERROR_NUMBER() AS ErrorNumber;
        print 'NO TABLE OR COLUMN FOUND !'
    END 

COMMIT  

If you are using C# and the Identity column is int, create a new instance of int without providing any value to it.It worked for me.

[identity_column] = new int()

Your example is simple and doesn’t require any additional table changes but generally speaking this is not so trivial.

If this column is referenced by other tables then you need to figure out what to do with other tables/columns. One option is to remove foreign keys and keep referenced data in other tables.

Another option is to find all referencing columns and remove them as well if they are not needed any longer.

In such cases the real challenge is finding all foreign keys. You can do this by querying system tables or using third party tools such as ApexSQL Search (free) or Red Gate Dependency tracker (premium but more features). There a whole thread on foreign keys here

To add columns in existing table:

ALTER TABLE table_name
 ADD
 column_name DATATYPE NULL  

To delete columns in existing table:

ALTER TABLE table_name
DROP COLUMN column_name

The simple answer to this is to use this:

ALTER TABLE MEN DROP COLUMN Lname;

More than one column can be specified like this:

ALTER TABLE MEN DROP COLUMN Lname, secondcol, thirdcol;

From SQL Server 2016 it is also possible to only drop the column only if it exists. This stops you getting an error when the column doesn't exist which is something you probably don't care about.

ALTER TABLE MEN DROP COLUMN IF EXISTS Lname;

There are some prerequisites to dropping columns. The columns dropped can't be:

• Used by an Index
• Used by CHECK, FOREIGN KEY, UNIQUE, or PRIMARY KEY constraints
• Associated with a DEFAULT
• Bound to a rule

If any of the above are true you need to drop those associations first.

Also, it should be noted, that dropping a column does not reclaim the space from the hard disk until the table's clustered index is rebuilt. As such it is often a good idea to follow the above with a table rebuild command like this:

ALTER TABLE MEN REBUILD;

Finally as some have said this can be slow and will probably lock the table for the duration. It is possible to create a new table with the desired structure and then rename like this:

SELECT 
   Fname 
   -- Note LName the column not wanted is not selected
INTO 
   new_MEN
FROM
   MEN;

EXEC sp_rename 'MEN', 'old_MEN';
EXEC sp_rename 'new_MEN', 'MEN';

DROP TABLE old_MEN;

But be warned there is a window for data loss of inserted rows here between the first select and the last rename command.

Generic:

ALTER TABLE table_name DROP COLUMN column_name;

In your case:

ALTER TABLE MEN DROP COLUMN Lname;

This can also be done through the SSMS GUI. The nice thing about this method is it warns you if there are any relationships on that column and can also automatically delete those as well.

1. Put table in Design view (right click on table) like so:

2. Right click on column in table's Design view and click "Delete Column"

As I stated before, if there are any relationships that would also need to be deleted, it will ask you at this point if you would like to delete those as well. You will likely need to do so to delete the column.

This is the correct answer:

ALTER TABLE MEN DROP COLUMN Lname

But... if a CONSTRAINT exists on the COLUMN, then you must DROP the CONSTRAINT first, then you will be able to DROP the COLUMN. In order to drop a CONSTRAINT, run:

ALTER TABLE MEN DROP CONSTRAINT {constraint_name_on_column_Lname}

In SQL Server 2016 you can use new DIE statements.

ALTER TABLE Table_name DROP COLUMN IF EXISTS Column_name

The above query is re-runnable it drops the column only if it exists in the table else it will not throw error.

Instead of using big IF wrappers to check the existence of column before dropping it you can just run the above DDL statement