8

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.

15

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.

0
1

We are also achieving this output using INFORMATION_SCHEMA. As Column Name is easy to understand for users to.

SELECT 
    'ALTER TABLE dbo.' + TABLE_NAME + ' ADD DateTime_Table DATETIME NULL;'
FROM 
    INFORMATION_SCHEMA.TABLES Tab
WHERE NOT EXISTS
    (
        SELECT 
            'X'
        FROM 
            INFORMATION_SCHEMA.COLUMNS  Col
        WHERE 
            Col.TABLE_NAME = Tab.TABLE_NAME
            AND Col.COLUMN_NAME='DateTime_Table'
    )
1
  • 1
    I like that you're checking for the column name before adding, can you explain if I can add other column name checks in the same place or do I need to rerun once per Column Name Jan 6 '17 at 17:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.