Check for Changes to an SQL Server Table

Check for changes to an SQL Server table?

Take a look at the CHECKSUM command:

SELECT CHECKSUM_AGG(BINARY_CHECKSUM(*)) FROM sample_table WITH (NOLOCK);

That will return the same number each time it's run as long as the table contents haven't changed. See my post on this for more information:

CHECKSUM

Here's how I used it to rebuild cache dependencies when tables changed:

ASP.NET 1.1 database cache dependency (without triggers)

Check for changes to a SQL Server database?

You might find the information in the transaction log...

Use

DBCC LOG(<database name>[,{0|1|2|3|4}])

0 - Basic Log Information (default)

1 - Lengthy Info

2 - Very Length Info

3 - Detailed

4 - Full

Example:

DBCC log (MY_DB, 4)

The result is somewhat cryptic and is better used with a third-party tool thought...

Credits: http://www.mssqlcity.com/Articles/KnowHow/ViewLog.htm

EDIT:

You might try

Select * from ::fn_dblog(null, null)

This page contains some usefull information about the results returned by the functions...
http://www.novicksoftware.com/udfofweek/Vol1/T-SQL-UDF-Volume-1-Number-27-fn_dblog.htm

Keep in mind, that those procedures are not for the public and therefore not easy to understand. The other possibility is to add triggers on all tables. But that is on the otherhand a lot of work.

How to monitor table structure changes in SQL Server?

You could use a ddl trigger on the database

create table dbo.logtablechanges
(
    tableobject_id int,
    tablename sysname,
    columnname sysname,--log a single column alteration
    theaction varchar(50),
    actionbylogin sysname constraint df_logtablechanges_actionbylogin default(original_login()),
    thestatement nvarchar(max),
    thedate datetime constraint df_logtablechanges_thedate default(getdate())
);
go

create table dbo.guineapigtbl(id int identity);
insert into dbo.guineapigtbl default values;
select * from guineapigtbl;
go

create trigger trig_db_alter_table on database
for ALTER_TABLE 
as
begin

    declare @d xml = EVENTDATA();

    declare @tblschemaname sysname, 
            @tblname sysname, 
            @action varchar(20),
            @colname sysname,
            @sqlcommand nvarchar(max);

    --for multiple columns      
    select 
        @tblschemaname = @d.value('(/EVENT_INSTANCE/SchemaName)[1]', 'sysname'),
        @tblname = @d.value('(/EVENT_INSTANCE/ObjectName)[1]', 'sysname'),
        @action = @d.value('local-name((/EVENT_INSTANCE/AlterTableActionList/*)[1])', 'varchar(20)'),
        --change this for multiple columns (depends on the logging)
        @colname = @d.value('(/EVENT_INSTANCE/AlterTableActionList/*[1]/Columns/Name)[1]', 'sysname'),
        @sqlcommand = @d.value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]', 'nvarchar(max)');

    if object_id(quotename(@tblschemaname) + '.' + quotename(@tblname)) = object_id('dbo.guineapigtbl')
      and @colname is not null
    begin
        insert into dbo.logtablechanges
        (
            tableobject_id, 
            tablename, columnname,  theaction, thestatement
        )
        values
        (
            object_id(quotename(@tblschemaname) + '.' + quotename(@tblname)),
            @tblname, @colname, @action, @sqlcommand
        )

    end

end
go

--testing
alter table dbo.guineapigtbl add col1 int;
alter table dbo.guineapigtbl add col2 varchar(10);
alter table dbo.guineapigtbl alter column col1 bigint;
alter table dbo.guineapigtbl drop column col1;

--multiple
alter table dbo.guineapigtbl add col3 int, col4 int;

--check log
select *
from logtablechanges

--cleanup
/*
drop table dbo.logtablechanges
drop table dbo.guineapigtbl
drop trigger trig_db_alter_table on database

go
*/

Detect changes on an sql-server table

SQL Dependency will not work for you, if you want to ensure no data is missed. SQL Dependency works only within the scope of your application registering to receive notifications. This means that if your application is down for any reason, you have missed a few notifications.

You will need to look at something closer to the database level itself to ensure that you get all notifications (data changes).

You can probably have triggers that update a staging table. Be careful when you use triggers. A failure or slow response in your triggers could affect the performance of the source database & operations.

You can have replication enabled and work on the replica data within your application and flag off any records that you have already processed.

SQL detect change in row

create table #log (name nvarchar(100), code nvarchar(100));

insert into #log values ('SARUMA','B01'), ('SARUMA','B01'), ('SARUMA','B01'), ('SARUMA','B01');
insert into #log values ('SARUMA','B02'), ('SARUMA','B02'), ('SARUMA','B02'), ('SARUMA','B02');
insert into #log values ('SARUMA','B03'), ('SARUMA','B03');

-- remove duplicates
with Singles (name, code) 
AS (
    select distinct name, code from #log
),
-- At first you need an order, in time? By alphanumerical code? Otherwise you cannot decide which is the first item you want to remove
-- So I added an identity ordering, but it is preferable to use a physical column
OrderedSingles (name, code, id) 
AS (
    select *, row_number() over(order by name)
    from Singles
)
-- Now self-join to get the next one, if the index is sequential you can join id = id+1
-- and take the join columns
select distinct ii.name, ii.Code
from OrderedSingles i
inner join OrderedSingles ii 
    on i.Name = ii.Name and i.Code <> ii.Code
where i.id < ii.Id;

How to monitor SQL Server table changes by using c#?

You can use the SqlDependency Class. Its intended use is mostly for ASP.NET pages (low number of client notifications).

ALTER DATABASE UrDb SET ENABLE_BROKER

Implement the OnChange event to get notified:

void OnChange(object sender, SqlNotificationEventArgs e)

And in code:

SqlCommand cmd = ...
cmd.Notification = null;

SqlDependency dependency = new SqlDependency(cmd);

dependency.OnChange += OnChange;

It uses the Service Broker (a message-based communication platform) to receive messages from the database engine.

Find recent object changes in SQL Server Database

Query the sys.objects table to find the objects that changed and filter by modify_date and type; U = User table, P = Stored procedure.

select * 
from sys.objects 
where (type = 'U' or type = 'P') 
  and modify_date > dateadd(m, -3, getdate()) 

This approach will tell you what objects have changed, but not the specific changes.

Find when the value changes in SQL Server

You can use lag():

select e.*
from (select e.*,
             lag(e.grade) over (partition by e.student order by e.examNum) as prev_grade
      from exam e
     ) e
where e.grade < e.prev_grade;

---

Related Topics