How can I remove tool generated columns named createdby updatedat etc-CodePudding

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?

CodePudding user response：

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')

CodePudding user response：

Here is an alternative solution, not as good as @dalek

Suck the schema into Visual Studio using a new db project and tool-schema-compare. Then do replace all using the regex below, replace with nothing.

This removes the unwanted column

\[CreatedAt\][^\,.] \,

this removes all the dangling commas at the end of a table column create that preceded the unwanted columns

\,([\s\r][\s] ) \)