I used a Redgate tool to synchronize data from a SQL Server database, and in the process, the tool created four new columns in each table with names like createdby
, updatedby
, etc.
Now that the data is in sync, I don't want these columns anymore.
Is there a simple way, maybe a script, to remove these columns?
You can drop the columns by running the following statement
ALTER TABLE table_name
DROP COLUMN column_name;
https://www.sqlservertutorial.net/sql-server-basics/sql-server-alter-table-drop-column/
EDIT: As Dale suggested, the intention might be to have a way to drop these columns en masse, so here's an update:
I tend to generate the code that does not have to be fully automated, but needs to be relatively easy to update. If I had dozens or hundreds of tables with extra columns that I want to remove, I would write a query similar to the one below, then copy the results from the lower pane in SSMS and execute the resulting script.
select 'alter table ' + quotename(table_schema) + '.' + quotename(table_name) + ' drop column ' + quotename(column_name)
from information_schema.columns
where 1=1
and column_name in ('createdby', 'updatedby')