Generic Approach to Identify Modified SQL Server Rows

By: Dinesh Asanka | Updated: 2018-01-30 | Comments | Related: > TSQL

Problem

In some cases, there is requirement to capture modified rows in SQL Server. There are several of ways this can be done, but in this tip I will show you a way that can be used with any version and any edition of SQL Server.

Solution

In SQL Server, there are several techniques to capture modified data changes. Replication is one of the most common techniques used by many database administrators. There are also other methods like, change tracking and change data capture (CDC) options which can be utilized out of the box. However, most of these options are either available only in latest SQL Server versions or only in the Enterprise edition. Triggers are also another mechanism implemented by many developers. However, since trigger are a synchronized approach, it will increase the transaction duration which could introduce performance issues to the system.

So, instead of using the above methods we are going to write some T-SQL code that can be used to show when records were last updated or created. The only requirement is that the table includes columns for Create date and Last Update date.

Create Sample Tables and Data

Let's first create some same tables to use for this excercise.

You will see below that I am using columns named LastUpdatedDate and CreatedDate to track when the records were created and updated. This is a requirement for use with the below scripts, but the column names could be named differently.

CREATE TABLE [dbo].[tblData](
[ID] [int] NOT NULL,
[Name] [varchar](50) NULL,
[LastUpdatedDate] [datetime] NULL,
[CreatedDate] [datetime] NULL,
CONSTRAINT [PK_tblData] PRIMARY KEY CLUSTERED 
(
[ID] ASC
)
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tblData2](
[ID] [int] NOT NULL,
[Name] [varchar](50) NOT NULL,
[LastUpdatedDate] [datetime] NULL,
[CreatedDate] [datetime] NULL,
CONSTRAINT [PK_tblData2] PRIMARY KEY CLUSTERED 
(
[ID] ASC,
[Name] ASC
)
) ON [PRIMARY]
GO

Now let's add some sample data to these two tables.

-- insert rows to tblData
INSERT [dbo].[tblData] ([ID], [Name], [LastUpdatedDate], [CreatedDate]) 
VALUES (1, N'Test', CAST(N'2017-12-26T09:30:00.000' AS DateTime), CAST(N'2017-12-25T09:30:00.000' AS DateTime))
INSERT [dbo].[tblData] ([ID], [Name], [LastUpdatedDate], [CreatedDate]) 
VALUES (2, N'Test2', NULL, CAST(N'2017-12-26T09:30:00.000' AS DateTime))
-- insert rows to tblData2
INSERT [dbo].[tblData2] ([ID], [Name], [LastUpdatedDate], [CreatedDate]) 
VALUES (1, N'S', CAST(N'2017-12-26T10:15:00.000' AS DateTime), CAST(N'2017-12-25T10:15:00.000' AS DateTime))
INSERT [dbo].[tblData2] ([ID], [Name], [LastUpdatedDate], [CreatedDate]) 
VALUES (2, N'D', NULL, CAST(N'2017-12-26T10:15:00.000' AS DateTime))
INSERT [dbo].[tblData2] ([ID], [Name], [LastUpdatedDate], [CreatedDate]) 
VALUES (3, N'C', NULL, CAST(N'2017-12-26T10:15:00.000' AS DateTime))

Script to Find Record Creation and Updates

The following script is used to identify modified data changes and this script is a generalized script which can be executed in any version of SQL Server from SQL Server 2000 to SQL Server 2016 and any edition of SQL Server such as Express to Enterprise.

SET NOCOUNT ON
--Following are the parameters you need to update
DECLARE @Date VARCHAR(50) = '2017-12-26'
DECLARE @ModifiedDateColumn VARCHAR(150) = 'LastUpdatedDate'
DECLARE @CreatedDateColumn VARCHAR(150) = 'CreatedDate'
DECLARE @TableName VARCHAR(150)
DECLARE @ColumnList VARCHAR(500) = ' '
DECLARE @SqlStatment VARCHAR(max)
DECLARE @ColumnListwithVarchar VARCHAR(max)
CREATE TABLE #TblSelect (
TableName VARCHAR(150),
PrimaryKeys VARCHAR(150),
PrimaryKeyValues VARCHAR(150),
UpdatedORCreated VARCHAR(1),
ModifiedDate DATETIME
)
CREATE TABLE #TableList (
TableName VARCHAR(150),
Updated VARCHAR(1)
)
INSERT INTO #TableList (
TableName,
Updated
)
SELECT DISTINCT Tab.TABLE_NAME,
'I'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = 'PRIMARY KEY'
WHILE (
SELECT Count(1)
FROM #TableList
WHERE Updated = 'I'
) > 0
BEGIN
SELECT TOP 1 @TableName = TableName
FROM #TableList
WHERE Updated = 'I'
SET @ColumnList = ' '
SELECT @ColumnList = @ColumnList + Col.COLUMN_NAME + ','
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = 'PRIMARY KEY'
AND Col.TABLE_NAME = @TableName
SET @ColumnListwithVarchar = ' '
SELECT @ColumnListwithVarchar = @ColumnListwithVarchar + 'CAST (' + Col.COLUMN_NAME + ' AS VARCHAR(150)),'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = 'PRIMARY KEY'
AND Col.TABLE_NAME = @TableName
SET @ColumnList = Substring(@ColumnList, 1, LEN(@ColumnList) - 1)
SET @ColumnListwithVarchar = Substring(@ColumnListwithVarchar, 1, LEN(@ColumnListwithVarchar) - 1)
SET @SqlStatment = 'INSERT INTO #TblSelect (TableName,PrimaryKeys,PrimaryKeyValues,UpdatedORCreated ,ModifiedDate) SELECT '
SET @SqlStatment = @SqlStatment + ' ''' + @TableName + ''',''' + @ColumnList + ''',' + REPLACE(@ColumnListwithVarchar, ',', ' + '' '' +') + ',''C'', ' + @CreatedDateColumn + ' FROM ' + @TableName
SET @SqlStatment = @SqlStatment + ' WHERE ' + @CreatedDateColumn + ' >= ''' + @Date + ''''
EXEC (@SqlStatment)
SET @SqlStatment = 'INSERT INTO #TblSelect (TableName,PrimaryKeys,PrimaryKeyValues,UpdatedORCreated ,ModifiedDate) SELECT '
SET @SqlStatment = @SqlStatment + ' ''' + @TableName + ''',''' + @ColumnList + ''',' + REPLACE(@ColumnListwithVarchar, ',', ' + '' '' +') + ',''U'', ' + @ModifiedDateColumn + ' FROM ' + @TableName
SET @SqlStatment = @SqlStatment + ' WHERE ' + @ModifiedDateColumn + ' >= ''' + @Date + ''''
EXEC (@SqlStatment)
DELETE
FROM #TableList
WHERE TableName = @TableName
END
SELECT *
FROM #TblSelect
DROP TABLE #TableList
DROP TABLE #TblSelect
GO

In this script, there are three parameters:

Date - this is show all retreive all changes with a date greater or equal to this value
ModifiedDateColumn - name of the modfied date column
CreatedDateColumn - name of the create date column

After creating the tables, adding some data and running the above script we have the following output. We can see there are three records that were created and two records updated.

In this output, the primary keys and values are returned. UpdatedOrCreated column indicates whether the record was updated or created.

SQL Server 2017 Script Version

The above script was written so that it can be executed in any version of SQL Server. However, the script can be modified to include new T-SQL features. CONCAT_WS was introduced in SQL Server 2017 and it can be utilized to concatenate multiple strings. Also, STRING_AGG function also can be used.

Here is another version of the script.

SET NOCOUNT ON
--Following are the parameters you need to select
DECLARE @Date VARCHAR(50) = '2017-12-26'
DECLARE @ModifiedDateColumn VARCHAR(150) = 'LastUpdatedDate'
DECLARE @CreatedDateColumn VARCHAR(150) = 'CreatedDate'
DECLARE @TableName VARCHAR(150)
DECLARE @ColumnList VARCHAR(500) = ' '
DECLARE @SqlStatment VARCHAR(max)
DECLARE @ColumnListwithVarchar VARCHAR(max)
CREATE TABLE #TblSelect (
TableName VARCHAR(150),
PrimaryKeys VARCHAR(150),
PrimaryKeyValues VARCHAR(150),
UpdatedORCreated VARCHAR(1),
ModifiedDate DATETIME
)
CREATE TABLE #TableList (
TableName VARCHAR(150),
Updated VARCHAR(1)
)
INSERT INTO #TableList (
TableName,
Updated
)
SELECT DISTINCT Tab.TABLE_NAME,
'I'
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = 'PRIMARY KEY'
WHILE (
SELECT Count(1)
FROM #TableList
WHERE Updated = 'I'
) > 0
BEGIN
SELECT TOP 1 @TableName = TableName
FROM #TableList
WHERE Updated = 'I'
SELECT @ColumnList = STRING_AGG(Col.COLUMN_NAME, ',')
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = 'PRIMARY KEY'
AND Col.TABLE_NAME = @TableName
PRINT @Columnlist
SELECT @ColumnListwithVarchar = @ColumnList
IF CHARINDEX(',', @ColumnList, 1) > 0
BEGIN
SET @ColumnListwithVarchar = 'CONCAT_WS('' '',' + @ColumnList + ')'
END
SET @SqlStatment = 'INSERT INTO #TblSelect (TableName,PrimaryKeys,PrimaryKeyValues,UpdatedORCreated ,ModifiedDate) SELECT '
SET @SqlStatment = @SqlStatment + ' ''' + @TableName + ''',''' + @ColumnList + ''',' + @ColumnListwithVarchar + ',''C'', ' + @CreatedDateColumn + ' FROM ' + @TableName
SET @SqlStatment = @SqlStatment + ' WHERE ' + @CreatedDateColumn + ' >= ''' + @Date + ''''
EXEC (@SqlStatment)
SET @SqlStatment = 'INSERT INTO #TblSelect (TableName,PrimaryKeys,PrimaryKeyValues,UpdatedORCreated ,ModifiedDate) SELECT '
SET @SqlStatment = @SqlStatment + ' ''' + @TableName + ''',''' + @ColumnList + ''',' + @ColumnListwithVarchar + ',''U'', ' + @ModifiedDateColumn + ' FROM ' + @TableName
SET @SqlStatment = @SqlStatment + ' WHERE ' + @ModifiedDateColumn + ' >= ''' + @Date + ''''
EXEC (@SqlStatment)
DELETE
FROM #TableList
WHERE TableName = @TableName
END
SELECT *
FROM #TblSelect
DROP TABLE #TableList
DROP TABLE #TblSelect

Summary

This is a pretty simple way to return a list of rows that where added or updated in your database.

A drawback of these scripts is the inability to get deleted records from a table. Also, the output only lists the rows primary key values, it doesn’t have the feature of returning the modified record.

Next Steps

Try out these scripts and try to improve these scripts to show which columns were modified.
Modify the script to include a start and end date range.
Modify the script to also include the ability to look at a list of tables instead of all tables.

About the author

Dinesh Asanka is a 10 time Data Platform MVP and frequent speaker at local and international conferences with more than 12 years of database experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2018-01-30