StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I use a special query that adds all these common columns (I call them audit columns) to all tables (using a cursor going over the information schema), which makes it easy to apply them to new databases.</p> <p>My columns are (SQL Server specific):</p> <pre> RowId UNIQUEIDENTIFIER NOT NULL DEFAULT (NEWID()), Created DATETIME NOT NULL DEFAULT (GETDATE()), Creator NVARCHAR(256) NOT NULL DEFAULT(SUSER_SNAME()) RowStamp TIMESTAMP NOT NULL </pre> <p>Now, in a fully normalised schema, I'd only need <code>RowId</code>, which would link to an Audit table containing the other rows. In fact, on reflection, I almost wish I had gone down this route - mainly because it makes the tables ugly (in fact I leave these columns out of database schema diagrams).</p> <p>However, when dealing with very large data sets, you do get a performance boost from having this data within the table, and I haven't experienced any problems with this system to date.</p> <p><strong>Edit:</strong> Might as well post the code to add the audit columns:</p> <pre> DECLARE AuditCursor CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0 AND TABLE_NAME NOT IN ('sysdiagrams') AND TABLE_NAME NOT LIKE 'dt_%' -- NB: you could change the above to only do it for certain tables OPEN AuditCursor DECLARE @schema varchar(128), @table varchar(128) FETCH NEXT FROM AuditCursor INTO @schema, @table WHILE @@FETCH_STATUS -1 BEGIN PRINT '* Adding audit columns to [' + @schema + '].[' + @table + ']...' IF NOT EXISTS (SELECT NULL FROM information_schema.columns WHERE table_schema = @schema AND table_name = @table AND column_name = 'Created') BEGIN DECLARE @sql_created varchar(max) SELECT @sql_created = 'ALTER TABLE [' + @schema + '].[' + @table + '] ADD [Created] DATETIME NOT NULL CONSTRAINT [DF_' + @table + '_Created] DEFAULT (GETDATE())' EXEC ( @sql_created ) PRINT ' - Added Created' END ELSE PRINT ' - Created already exists, skipping' IF NOT EXISTS (SELECT NULL FROM information_schema.columns WHERE table_schema = @schema AND table_name = @table AND column_name = 'Creator') BEGIN DECLARE @sql_creator varchar(max) SELECT @sql_creator = 'ALTER TABLE [' + @schema + '].[' + @table + '] ADD [Creator] VARCHAR(256) NOT NULL CONSTRAINT [DF_' + @table + '_Creator] DEFAULT (SUSER_SNAME())' EXEC ( @sql_creator ) PRINT ' - Added Creator' END ELSE PRINT ' - Creator already exists, skipping' IF NOT EXISTS (SELECT NULL FROM information_schema.columns WHERE table_schema = @schema AND table_name = @table AND column_name = 'RowId') BEGIN DECLARE @sql_rowid varchar(max) SELECT @sql_rowid = 'ALTER TABLE [' + @schema + '].[' + @table + '] ADD [RowId] UNIQUEIDENTIFIER NOT NULL CONSTRAINT [DF_' + @table + '_RowId] DEFAULT (NEWID())' EXEC ( @sql_rowid ) PRINT ' - Added RowId' END ELSE PRINT ' - RowId already exists, skipping' IF NOT EXISTS (SELECT NULL FROM information_schema.columns WHERE table_schema = @schema AND table_name = @table AND (column_name = 'RowStamp' OR data_type = 'timestamp')) BEGIN DECLARE @sql_rowstamp varchar(max) SELECT @sql_rowstamp = 'ALTER TABLE [' + @schema + '].[' + @table + '] ADD [RowStamp] ROWVERSION NOT NULL' EXEC ( @sql_rowstamp ) PRINT ' - Added RowStamp' END ELSE PRINT ' - RowStamp or another timestamp already exists, skipping' -- basic tamper protection against non-SA users PRINT ' - setting DENY permission on audit columns' DECLARE @sql_deny VARCHAR(1000) SELECT @sql_deny = 'DENY UPDATE ON [' + @schema + '].[' + @table + '] ([Created]) TO [public]' + 'DENY UPDATE ON [' + @schema + '].[' + @table + '] ([RowId]) TO [public]' + 'DENY UPDATE ON [' + @schema + '].[' + @table + '] ([Creator]) TO [public]' EXEC (@sql_deny) PRINT '* Completed processing [' + @schema + '].[' + @table + ']' FETCH NEXT FROM AuditCursor INTO @schema, @table END CLOSE AuditCursor DEALLOCATE AuditCursor GO </pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload