Sql-server – Add columns to all tables in a database if the columns don’t exist

auditsql server

I have to implement auditing in a database with many (50+) tables. I am looking to add the user name and the time when the user altered the record as columns to all tables. However, some tables have the columns already, so I don't need to add the columns to all tables. Assuming the column names are DateTime_Table and UserName_Table, how can I add DateTime_Table column and UserName_Table columns to all tables in the database if the columns don't exist?

I saw this loop for Postgres but I need help in SQL Server.

Best Answer

If you only have around 50 tables, I personally wouldn't bother with a solution that uses a cursor. You can write a SQL query that looks at SQL Server object catalog views to generate the SQL code that you need to run to add the columns. One nice benefit is that you can easily review your code before running it. I think that the below query will be close to what you need for the DateTime_Table column:

SELECT 'ALTER TABLE ' + QUOTENAME(ss.name) + '.' + QUOTENAME(st.name) + ' ADD [DateTime_Table] DATETIME NULL;'
FROM sys.tables st
INNER JOIN sys.schemas ss on st.[schema_id] = ss.[schema_id]
WHERE st.is_ms_shipped = 0
AND NOT EXISTS (
    SELECT 1
    FROM sys.columns sc
    WHERE sc.[object_id] = st.[object_id]
    AND sc.name = 'DateTime_Table'
);

I used QUOTENAME to avoid issues with special characters in table or schema names. Once you've tested the generated code and are confident that it is correct, you can simply copy and paste the SSMS query results into another query window and run them.

If you needed a more automated approach or needed to deal with many more objects, you could use a cursor to find and loop through each table missing the column and EXEC to execute dynamic SQL to add the column.