· 9 years ago · Nov 30, 2016, 12:02 PM
1
2-- ===============================
3-- Create the base audit generation sproc
4-- Modified heavily, original source unknown
5-- ===============================
6
7CREATE PROCEDURE [audit].[CreateAuditObjects]
8 @TableName NVARCHAR(128),
9 @Owner NVARCHAR(128) = 'dbo',
10 @AuditSchema VARCHAR(128) = 'audit',
11 @AuditTableNamePrefix NVARCHAR(128) = '',
12 @DropAuditTable BIT = 0
13AS
14BEGIN
15
16 -- Check if table exists
17 IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[' + @Owner + '].[' + @TableName + ']') AND OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN
18 PRINT 'ERROR: Table does not exist'
19 RETURN
20 END
21
22
23 -- Check @AuditTableNamePrefix
24 IF @AuditTableNamePrefix IS NULL BEGIN
25 PRINT 'ERROR: @AuditTableNamePrefix cannot be null. Use an empty string instead'
26 RETURN
27 END
28
29
30 -- Drop audit table if it exists and drop should be forced
31 IF @DropAuditTable = 1 BEGIN
32 IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[' + @Owner + '].[' + @AuditTableNamePrefix + @TableName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1) BEGIN
33 PRINT 'Dropping audit table [' + @Owner + '].[' + @AuditTableNamePrefix + @TableName + ']'
34 EXEC ('drop table ' + @AuditTableNamePrefix + @TableName)
35 END
36 END
37
38
39 -- Declare cursor to loop over all columns in source table
40 DECLARE TableColumns CURSOR READ_ONLY FOR
41 SELECT b.name, c.name as TypeName, b.length, b.isnullable, b.collation, b.xprec, b.xscale
42 FROM sysobjects a
43 INNER JOIN syscolumns b on a.id = b.id
44 INNER JOIN systypes c on b.xtype = c.xtype and c.name <> 'sysname'
45 WHERE a.id = OBJECT_ID(N'[' + @Owner + '].[' + @TableName + ']')
46 AND OBJECTPROPERTY(a.id, N'IsUserTable') = 1
47 ORDER BY b.colId
48
49
50
51 -- Declare temp variable to fetch records into
52 DECLARE @ColumnName VARCHAR(128)
53 DECLARE @ColumnType VARCHAR(128)
54 DECLARE @ColumnLength SMALLINT
55 DECLARE @ColumnNullable INT
56 DECLARE @ColumnCollation SYSNAME
57 DECLARE @ColumnPrecision TINYINT
58 DECLARE @ColumnScale TINYINT
59
60 DECLARE @ListOfFields VARCHAR(MAX)
61 SET @ListOfFields = ''
62
63
64 -- Obtain list of fields
65 OPEN TableColumns
66
67 FETCH Next FROM TableColumns INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
68
69 WHILE @@FETCH_STATUS = 0 BEGIN
70 IF (@ColumnType <> 'text' AND @ColumnType <> 'ntext' AND @ColumnType <> 'image' AND @ColumnType <> 'timestamp') BEGIN
71 SET @ListOfFields = @ListOfFields + '[' + @ColumnName + '],'
72 END
73 FETCH Next FROM TableColumns INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
74 END
75
76 CLOSE TableColumns
77
78 PRINT 'FIELDS: ' + @ListOfFields
79
80
81 -- Check if audit table exists
82 IF EXISTS (
83 SELECT *
84 FROM dbo.sysobjects
85 WHERE id = OBJECT_ID(N'[' + @AuditSchema + '].[' + @AuditTableNamePrefix + @TableName + ']')
86 AND OBJECTPROPERTY(id, N'IsUserTable') = 1
87 ) BEGIN
88
89 -- Audit Table Exists, get a list of any updated fields and build the Audit's ALTER statement accordingly
90
91 DECLARE @AlterStatement VARCHAR(MAX)
92 SET @AlterStatement = ''
93
94 OPEN TableColumns
95
96 FETCH Next FROM TableColumns INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
97
98 WHILE @@FETCH_STATUS = 0 BEGIN
99
100 -- if the field doesn't already exist on the audit table, alter it in
101 DECLARE @ExistingColumnName VARCHAR(128)
102 SET @ExistingColumnName = (SELECT TOP 1 Name FROM sys.Columns WHERE Name = @ColumnName AND Object_ID = OBJECT_ID(N'[' + @AuditSchema + '].[' + @AuditTableNamePrefix + @TableName + ']'))
103
104 PRINT 'Existing Column Check: ' + COALESCE(@ExistingColumnName, 'NULL')
105
106 IF (COALESCE(@ExistingColumnName, '') = '') BEGIN
107
108 IF (@ColumnType <> 'text'
109 AND @ColumnType <> 'ntext'
110 AND @ColumnType <> 'image'
111 AND @ColumnType <> 'timestamp') BEGIN
112
113 PRINT 'Setting Alter Statement'
114
115 SET @AlterStatement = @AlterStatement + ' ALTER TABLE [' + @AuditSchema + '].[' + @AuditTableNamePrefix + @TableName + '] ADD [' + @ColumnName + '] [' + @ColumnType + '] '
116
117 IF @ColumnType IN ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar') BEGIN
118 IF (@ColumnLength = -1)
119 Set @AlterStatement = @AlterStatement + '(MAX) '
120 ELSE
121 SET @AlterStatement = @AlterStatement + '(' + CAST(@ColumnLength AS VARCHAR(10)) + ') '
122 END
123
124 IF @ColumnType IN ('decimal', 'numeric')
125 SET @AlterStatement = @AlterStatement + '(' + CAST(@ColumnPrecision AS VARCHAR(10)) + ',' + CAST(@ColumnScale AS VARCHAR(10)) + ') '
126
127 IF @ColumnType IN ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
128 SET @AlterStatement = @AlterStatement + 'COLLATE ' + @ColumnCollation + ' '
129
130 IF @ColumnNullable = 0
131 SET @AlterStatement = @AlterStatement + 'NOT '
132
133 SET @AlterStatement = @AlterStatement + 'NULL; '
134
135 END
136
137 END
138
139 FETCH Next FROM TableColumns INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
140
141 END
142
143 CLOSE TableColumns
144
145 -- Alter the audit table
146 IF @AlterStatement = '' BEGIN
147 PRINT 'No updates to audit table, returning'
148 DEALLOCATE TableColumns
149 RETURN
150 END ELSE BEGIN
151 PRINT 'Altering audit table [' + @AuditSchema + '].[' + @AuditTableNamePrefix + @TableName + ']:'
152 PRINT @AlterStatement
153 EXEC (@AlterStatement)
154 END
155
156 END ELSE BEGIN
157
158 -- AuditTable does not exist, create new
159
160 DECLARE @CreateStatement VARCHAR(MAX)
161
162 -- Start of create table
163 SET @CreateStatement = 'CREATE TABLE [' + @AuditSchema + '].[' + @AuditTableNamePrefix + @TableName + '] ('
164 SET @CreateStatement = @CreateStatement + '[AuditId] [INT] IDENTITY (1, 1) NOT NULL,'
165
166
167 -- Loop all columns and build the create accordingly
168 OPEN TableColumns
169
170 FETCH Next FROM TableColumns INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
171
172 WHILE @@FETCH_STATUS = 0 BEGIN
173
174 IF (@ColumnType <> 'text'
175 AND @ColumnType <> 'ntext'
176 AND @ColumnType <> 'image'
177 AND @ColumnType <> 'timestamp') BEGIN
178
179 SET @CreateStatement = @CreateStatement + '[' + @ColumnName + '] [' + @ColumnType + '] '
180
181 IF @ColumnType IN ('binary', 'char', 'nchar', 'nvarchar', 'varbinary', 'varchar') BEGIN
182 IF (@ColumnLength = -1)
183 Set @CreateStatement = @CreateStatement + '(MAX) '
184 ELSE
185 SET @CreateStatement = @CreateStatement + '(' + CAST(@ColumnLength AS VARCHAR(10)) + ') '
186 END
187
188 IF @ColumnType IN ('decimal', 'numeric')
189 SET @CreateStatement = @CreateStatement + '(' + CAST(@ColumnPrecision AS VARCHAR(10)) + ',' + CAST(@ColumnScale AS VARCHAR(10)) + ') '
190
191 IF @ColumnType IN ('char', 'nchar', 'nvarchar', 'varchar', 'text', 'ntext')
192 SET @CreateStatement = @CreateStatement + 'COLLATE ' + @ColumnCollation + ' '
193
194 IF @ColumnNullable = 0
195 SET @CreateStatement = @CreateStatement + 'NOT '
196
197 SET @CreateStatement = @CreateStatement + 'NULL, '
198
199 END
200
201 FETCH Next FROM TableColumns INTO @ColumnName, @ColumnType, @ColumnLength, @ColumnNullable, @ColumnCollation, @ColumnPrecision, @ColumnScale
202
203 END
204
205 CLOSE TableColumns
206
207 SET @CreateStatement = @CreateStatement + '[AuditAction] [CHAR] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,'
208 SET @CreateStatement = @CreateStatement + '[AuditDate] [DATETIME] NOT NULL ,'
209 SET @CreateStatement = @CreateStatement + '[AuditUser] [VARCHAR] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,'
210 SET @CreateStatement = @CreateStatement + '[AuditApp] [VARCHAR](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,'
211 SET @CreateStatement = @CreateStatement + '[AuditContext] [INT])'
212
213 -- Create audit table
214 PRINT 'Creating audit table [' + @AuditSchema + '].[' + @AuditTableNamePrefix + @TableName + ']:'
215 PRINT @CreateStatement
216 EXEC (@CreateStatement)
217
218 -- Set primary key and default values
219 SET @CreateStatement = 'ALTER TABLE [' + @AuditSchema + '].[' + @AuditTableNamePrefix + @TableName + '] ADD '
220 SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @AuditTableNamePrefix + @TableName + '_AuditDate] DEFAULT (getdate()) FOR [AuditDate],'
221 SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @AuditTableNamePrefix + @TableName + '_AuditUser] DEFAULT (suser_sname()) FOR [AuditUser],CONSTRAINT [PK_' + @AuditTableNamePrefix + @TableName + '] PRIMARY KEY CLUSTERED '
222 SET @CreateStatement = @CreateStatement + '([AuditId]), '
223 SET @CreateStatement = @CreateStatement + 'CONSTRAINT [DF_' + @AuditTableNamePrefix + @TableName + '_AuditApp] DEFAULT (app_name()) for [AuditApp]'
224
225 EXEC (@CreateStatement)
226
227 END
228
229 DEALLOCATE TableColumns
230
231
232
233 -- Drop Triggers, if they exist
234 PRINT 'Dropping triggers'
235 IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Insert]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
236 EXEC ('drop trigger [' + @Owner + '].[tr_audit_' + @TableName + '_Insert]')
237
238 IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Update]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
239 EXEC ('drop trigger [' + @Owner + '].[tr_audit_' + @TableName + '_Update]')
240
241 IF exists (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[' + @Owner + '].[tr_' + @TableName + '_Delete]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
242 EXEC ('drop trigger [' + @Owner + '].[tr_audit_' + @TableName + '_Delete]')
243
244
245 -- Create triggers
246 PRINT 'Creating triggers'
247 EXEC ('CREATE TRIGGER [tr_audit_' + @TableName + '_Insert] ON [' + @Owner + '].[' + @TableName + '] FOR INSERT AS DECLARE @Context INT SET @Context = (audit.GetContext()) INSERT INTO [' + @AuditSchema + '].[' + @AuditTableNamePrefix + @TableName + '] (' + @ListOfFields + 'AuditAction, AuditContext) SELECT ' + @ListOfFields + '''I'', @Context FROM Inserted')
248
249 EXEC ('CREATE TRIGGER [tr_audit_' + @TableName + '_Update] ON [' + @Owner + '].[' + @TableName + '] FOR UPDATE AS DECLARE @Context INT SET @Context = (audit.GetContext()) INSERT INTO [' + @AuditSchema + '].[' + @AuditTableNamePrefix + @TableName + '](' + @ListOfFields + 'AuditAction, AuditContext) SELECT ' + @ListOfFields + '''U'', @Context FROM Inserted')
250
251 EXEC ('CREATE TRIGGER [tr_audit_' + @TableName + '_Delete] ON [' + @Owner + '].[' + @TableName + '] FOR DELETE AS DECLARE @Context INT SET @Context = (audit.GetContext()) INSERT INTO [' + @AuditSchema + '].[' + @AuditTableNamePrefix + @TableName + '](' + @ListOfFields + 'AuditAction, AuditContext) SELECT ' + @ListOfFields + '''D'', @Context FROM Deleted')
252
253END
254
255
256
257GO