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.
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:
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