Skip to main content

Check for changes to an SQL Server table?

Problem: Suppose your team is working on the under-development project so it might be possible continuous work on the database and perform changes in Table, Stored procedure as per requirement, and daily you have to update the testing server database as per changes are done in developing server database then how it is possible to trace those changes. 
There are a lot of solutions for this problem which is listed below
Solution 1:  
For SQL Server 2000, 2005 and above use the CHECKSUM command
SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM Your_Table_Name WITH (NOLOCK);
That will return the same number each time its run as long as the table contents haven't changed. 
Unfortunately CHECKSUM does not work always properly to detect changes. It is only a primitive checksum and no CRC calculation. Therefore you can't use it to detect all changes, e. g. symmetrical changes result in the same CHECKSUM! 
Solution 2:
1.   Run the following query. Before executing query replace DB_Name with your database name you want to trace in this profiler, also change the file path with appropriate file name. In following example your file will be saved at location 'C:/Folder/MyFile.trc' with .trc extension.
USE[DB_NAME]
GO
DECLARE @RC int, 
  @TraceID int, 
  @on BIT,
  @maxfilesize bigint
  @DateTime datetime;
EXEC @rc = sp_trace_create @TraceID output, 2, N'C:\Folder\MyFile',@maxfilesize, @Datetime
SELECT RC = @RC, TraceID = @TraceID
-- Set the event
SELECT @on = 1
EXEC sp_trace_setevent @TraceID, 128, 1, @on
EXEC sp_trace_setevent @TraceID, 128, 11, @on
EXEC sp_trace_setevent @TraceID, 128, 14, @on
EXEC sp_trace_setevent @TraceID, 128, 35, @on
EXEC @RC = sp_trace_setstatus @TraceID, 1
GO
2.      If you want to import records of trace into table the use following query.
SELECT * INTO tablename FROM ::fn_trace_gettable('C:\Folder\MyFile.trc', DEFAULT)
GO
3.  Now you are ready to see your trace records, for example
SELECT TOP 1000 [TextData] ,[HostName] ,[LoginName] ,[StartTime] ,[EndTime] ,[ServerName] ,[EventClass]
FROM [DBname].[dbo].[tablename]
WHERE DBname.dbo.tablename.TextData IS NOT NULL
 4. Execute this query to stop the trace when you want to audit data:
sp_trace_setstatus @traceid = 1, @status = 0

5.      Inspect the "TextData" column for the events: CREATE DATABASE, DROP DATABASE, ALTER DATABASE in order to know what database was changed. To find out who changed it and when it was changed, refer to the "LoginName" and "StartTime" columns respectively.

CHECKSUM
Solution 3: Use some "SQL Source Control" a third party tool provided by red-get, please follow the link: https://www.red-gate.com/products/sql-development/sql-source-control/ 
Solution 4: If you want to see only changes the run the following query
select *
from sys.objects
where (type = 'U' or type = 'P')
  and modify_date > dateadd(m, -3, getdate())