Auditing Columns Add:
I had a need to answer “who”,”where” and “when” questions about the data we add and update.
We didn’t have Auditing columns on all our tables. So I thought about coming up with easy way of Adding Auditing Columns.
Auditing Columns:
CreatedBy Default to USER_NAME()
CreatedDateTime Default to GETUTCDATE()
UpdatedBy
UpdatedDateTime
Adding Auditing Columns Script:
Add Above columns to the tables specified.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 |
DECLARE @pTableName SYSNAME , @pColumnName SYSNAME , @pSchemaName SYSNAME , @pdatatype sysname , @pDefault sysname , @sql NVARCHAR(MAX) DECLARE @ColumnNames TABLE ( ColumnName sysname) INSERT INTO @ColumnNames VALUES ('CreatedBy') ,('CreatedDateTime'),('UpdatedBy'),('UpdatedDateTime') DECLARE COLUMN_NAME_CURSOR CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME , c.ColumnName COLUMN_NAME FROM INFORMATION_SCHEMA.TABLES CROSS JOIN @ColumnNames c WHERE TABLE_NAME IN ( 'TableName1' ,'TableName2' ) OPEN COLUMN_NAME_CURSOR FETCH NEXT FROM COLUMN_NAME_CURSOR INTO @pSchemaName , @pTableName, @pColumnName WHILE @@FETCH_STATUS = 0 BEGIN IF @pColumnName = 'CreatedBy' BEGIN SELECT @pdatatype = 'nvarchar(50)' , @pDefault = '(SUSER_SNAME())' END IF @pColumnName = 'CreatedDateTime' BEGIN SELECT @pdatatype = '[DATETIME]' , @pDefault = '(GETUTCDATE())' END IF @pColumnName = 'UpdatedBy' BEGIN SELECT @pdatatype = 'nvarchar(50)' , @pDefault = NULL END IF @pColumnName = 'UpdatedDateTime' BEGIN SELECT @pdatatype = '[DATETIME]' , @pDefault = NULL END SET @sql = ' IF NOT EXISTS (Select 1 From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = ''' + @pTableName + ''' and TABLE_SCHEMA=''' + @pSchemaName + ''' and COLUMN_NAME = ''' + @pColumnName + ''') BEGIN print ''Added Column ' + @pColumnName + ' to ' + @pSchemaName + '.' + @pTableName + ' table'' Alter Table ' + @pSchemaName + '.' + @pTableName + ' ADD ' + @pColumnName + ' ' + @pdatatype + CASE WHEN @pDefault IS NOT NULL THEN ' DEFAULT(' + @pDefault + ')' ELSE '' END + ' END ELSE BEGIN print ''Column ' + @pColumnName + ' already exists in the table ' + @pSchemaName + '.' + @pTableName + ''' END ' PRINT ( @sql ) EXEC sys.sp_executesql @sql FETCH NEXT FROM COLUMN_NAME_CURSOR INTO @pSchemaName , @pTableName, @pColumnName END CLOSE COLUMN_NAME_CURSOR DEALLOCATE COLUMN_NAME_CURSOR |
ROLLBACK Script:
Drop Constraints and drop Columns Script:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 |
DECLARE @pTableName SYSNAME , @pColumnName SYSNAME , @pSchemaName SYSNAME , @pConstraintName sysname , @pdatatype sysname , @pDefault sysname , @sql NVARCHAR(MAX) DECLARE @ColumnNames TABLE ( ColumnName sysname) INSERT INTO @ColumnNames VALUES ('CreatedBy') ,('CreatedDateTime'),('UpdatedBy'),('UpdatedDateTime') DECLARE COLUMN_NAME_CURSOR CURSOR FOR SELECT DISTINCT tabl.TABLE_SCHEMA, tabl.TABLE_NAME , COL_NAME(const.parent_object_id,const.parent_column_id) COLUMN_NAME , const.name FROM INFORMATION_SCHEMA.TABLES tabl JOIN INFORMATION_SCHEMA.COLUMNS columns ON columns.TABLE_NAME = tabl.TABLE_NAME AND columns.TABLE_SCHEMA = tabl.TABLE_SCHEMA AND columns.TABLE_CATALOG = tabl.TABLE_CATALOG JOIN sys.default_constraints const ON OBJECT_NAME(const.parent_object_id) = tabl.TABLE_NAME AND SCHEMA_NAME(const.schema_id)= tabl.TABLE_SCHEMA AND const.type = 'D' AND tabl.TABLE_CATALOG = DB_NAME() AND columns.COLUMN_NAME LIKE '%Created_DTTM%' AND COL_NAME(const.parent_object_id,const.parent_column_id) IN (SELECT ColumnName FROM @ColumnNames ) OPEN COLUMN_NAME_CURSOR FETCH NEXT FROM COLUMN_NAME_CURSOR INTO @pSchemaName , @pTableName, @pColumnName , @pConstraintName WHILE @@FETCH_STATUS = 0 BEGIN IF @pColumnName = 'CreatedBy' BEGIN SELECT @pdatatype = 'nvarchar(50)' , @pDefault = '(SUSER_SNAME())' END IF @pColumnName = 'CreatedDateTime' BEGIN SELECT @pdatatype = '[DATETIME]' , @pDefault = '(GETUTCDATE())' END IF @pColumnName = 'UpdatedBy' BEGIN SELECT @pdatatype = 'nvarchar(50)' , @pDefault = NULL END IF @pColumnName = 'UpdatedDateTime' BEGIN SELECT @pdatatype = '[DATETIME]' , @pDefault = NULL END SET @sql = ' IF EXISTS (Select 1 From sys.default_constraints Where name = ''' + @pConstraintName + ''') BEGIN print ''Removed CONSTRAINT ON TABLE ' + @pColumnName + ' to ' + @pSchemaName + '.'+ ': ConstraintName ":'+ @pConstraintName + @pTableName + ' table'' ALTER TABLE ' + @pSchemaName + '.' + @pTableName + ' DROP ' + @pConstraintName +' END ELSE BEGIN print ''Constraint ' + @pConstraintName + ' does not exist in the table ' + @pSchemaName + '.' + @pTableName + ''' END ' PRINT ( @sql ) EXEC sys.sp_executesql @sql FETCH NEXT FROM COLUMN_NAME_CURSOR INTO @pSchemaName , @pTableName, @pColumnName , @pConstraintName END CLOSE COLUMN_NAME_CURSOR DEALLOCATE COLUMN_NAME_CURSOR GO DECLARE @pTableName SYSNAME , @pColumnName SYSNAME , @pSchemaName SYSNAME , @pdatatype sysname , @pDefault sysname , @sql NVARCHAR(MAX) DECLARE @ColumnNames TABLE ( ColumnName sysname) INSERT INTO @ColumnNames VALUES ('CreatedBy') ,('CreatedDateTime'),('UpdatedBy'),('UpdatedDateTime') DECLARE COLUMN_NAME_CURSOR CURSOR FOR SELECT tabl.TABLE_SCHEMA, tabl.TABLE_NAME , c.ColumnName COLUMN_NAME FROM INFORMATION_SCHEMA.TABLES tabl CROSS JOIN @ColumnNames c JOIN INFORMATION_SCHEMA.COLUMNS columns ON columns.TABLE_NAME = tabl.TABLE_NAME AND columns.TABLE_SCHEMA = tabl.TABLE_SCHEMA AND columns.TABLE_CATALOG = tabl.TABLE_CATALOG OPEN COLUMN_NAME_CURSOR FETCH NEXT FROM COLUMN_NAME_CURSOR INTO @pSchemaName , @pTableName, @pColumnName WHILE @@FETCH_STATUS = 0 BEGIN IF @pColumnName = 'CreatedBy' BEGIN SELECT @pdatatype = 'nvarchar(50)' , @pDefault = '(SUSER_SNAME())' END IF @pColumnName = 'CreatedDateTime' BEGIN SELECT @pdatatype = '[DATETIME]' , @pDefault = '(GETUTCDATE())' END IF @pColumnName = 'UpdatedBy' BEGIN SELECT @pdatatype = 'nvarchar(50)' , @pDefault = NULL END IF @pColumnName = 'UpdatedDateTime' BEGIN SELECT @pdatatype = '[DATETIME]' , @pDefault = NULL END SET @sql = ' IF EXISTS (Select 1 From INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = ''' + @pTableName + ''' and TABLE_SCHEMA=''' + @pSchemaName + ''' and COLUMN_NAME = ''' + @pColumnName + ''') BEGIN print ''Removed Column ' + @pColumnName + ' to ' + @pSchemaName + '.' + @pTableName + ' table'' ALTER TABLE ' + @pSchemaName + '.' + @pTableName + ' DROP COLUMN ' + @pColumnName +' END ELSE BEGIN print ''Column ' + @pColumnName + ' does not exist in the table ' + @pSchemaName + '.' + @pTableName + ''' END ' PRINT ( @sql ) EXEC sys.sp_executesql @sql FETCH NEXT FROM COLUMN_NAME_CURSOR INTO @pSchemaName , @pTableName, @pColumnName END CLOSE COLUMN_NAME_CURSOR DEALLOCATE COLUMN_NAME_CURSOR GO |