Sooner or later, it happens. Someone asks “When this data has been modified? Who changed this information?”
A reliable system must ensure this information could be obtained any time.
In this post I offer you a simple way to add columns to any table you want to preserve the metadata about the last changes.
In another post, I’ll talk about tracking all the changes.
Like in the last posts, I prepare it as stored procedure, which receives two parameters:
- The Table Name
- The Schema Name (optional) in which case, “dbo” is assumed
The procedure will add the following columns:
|Column Name||Information Stored||Default Value|
|Created by||username in the insert action||CURRENT_USER|
|Modified By||username in the update action|
|Creation Date||date & time of the creation||SYSUTCDATETIME|
|Modification Date||date & time of the update|
Some code highlights
Using a select from INFORMATION_SCHEMA.COLUMNS the procedure detects if the column to add already exists.
DECLARE @Isthere INT; DECLARE @CheckForColumn NVARCHAR(300) = 'select @Isthere =count(*) from INFORMATION_SCHEMA.COLUMNS T where TABLE_SCHEMA=@schema and TABLE_NAME=@TableName and T.COLUMN_NAME=@ColumnName'
Like in other examples I already post, using sp_executesql stored procedure, can execute the T-SQL sentence passing parameters.
DECLARE @ColumnName SYSNAME = 'Created by'; EXEC [sp_executesql] @CheckForColumn , N'@Schema sysname,@TableName sysname,@ColumnName sysname,@Isthere int OUTPUT' , @schema , @TableName , @ColumnName , @Isthere OUTPUT;
If the column doesn’t exist, two other dynamic T-SQL sentences are executed, adding the column and later configuring the default value.
DECLARE @SqlToExecute NVARCHAR(MAX); IF @Isthere = 0 BEGIN SET @SqlToExecute = 'ALTER TABLE [' + @schema + '].[' + @TableName + '] ADD [' + @columnName + '] nvarchar(150) NULL'; EXEC [sp_executesql] @SqlToExecute; SET @SqlToExecute = 'ALTER TABLE [' + @schema + '].[' + @TableName + '] ADD CONSTRAINT [DF_' + @TableName + '_' + @columnName + '] DEFAULT CURRENT_USER FOR [' + @columnName + ']'; EXEC [sp_executesql] @SqlToExecute; END;
The same steps are repeated for the other columns.
In similar way with other previous posts, this stored procedure could be executed over all the tables in a database, using a select from INFORMATION_SCHEMA.TABLES.
You can find the procedure in my repository here.