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())

Popular posts from this blog

Remove special character from string in MongoDB

Problem: Suppose wehave a collection and one field is type string contains some special character (like !@#$%) and we don’t want these special character.
Solution: We can easily remove the special character from field using script “replace(/[^a-zA-Z 0-9 ]/g, '')” in our query.  How can we remove special character from string using this script please see following example.
Example: Suppose we have a collection “EduSurvey “where we are collecting information from institutions.

{Name:"JB institute”, About:"This is good one collage for MBA", Information:"This $%%institute ##has good faculty etc$$"}
{Name:"MK institute”, About:"This is good one collage for MCA", Information:"This$$%# is the dummy text12"}
{Name:"MG institute”, About:"This is good one collage for B,Tech", Information:"This# institute@ has&* good infrastructure"}

Did you notice Information fields contains some special character so we…

Add day to ISODate in MongoDB

We can use $add operator to add days in ISODate in mongodb, $add is the Arithmetic Aggregation Operator which adds number and date in mongodb.
Syntax:

{ $add: [ <expression1>, <expression2>, ... ] }

Note:  If one of the argument is date $add operator treats to other arguments as milliseconds to add to the date.
Example: Suppose we have a Test collection as below.

{"Title" : "Add day to ISODate in MongoBD","CreatedDate" : ISODate("2016-07-07T08:00:00.000Z")}

Query to add 2 days in CreatedDate

db.Test.aggregate([      { $project: { Title: 1, AddedDate: { $add: [ "$CreatedDate", 2*24*60*60000 ] } } }    ])

Result:

{ "_id" : ObjectId("579a1567ac1b3f3732483de0"), "Title" : "Add day to ISODate in MongoBD", "AddedDate" : ISODate("2016-07-09T08:00:00.000Z") }

Note: As mentioned in above note we have to convert days in millisecond because $add operator treat to other arg…