sql-serverredgate

How can I remove tool generated columns named createdby updatedat etc


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?


Solution

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