Auditing Columns Add

Posted on January 16, 2015 by

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

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

(

'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

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

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

Auditing Columns Add

Recent Posts

Categories

Recent Posts

Archives