Table level Change Tarcking - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Table level Change Tarcking

Author

Topic

dsarath
Starting Member

3 Posts

Posted - 2009-05-29 : 08:38:05

Hi all...
My requirement is like catching the changes in all tables in different databases..

if i select one database,it will list out all the tables in the database..with in that tables,i need to select one table,and timeperiod.
it should display the all changes in the table(insert,update,delete)

required fields are :-Change time,Opertaion(ins,del,update),before changing(data),after cahnging(data)

how to track this..
iam trying with triggers to save the details in separate table,but here all the things are dynamic(no. of databases,no.of tables,no.of columns in a table).so even we cant write table column names also in trigger..

help me

sarath

a.rameshk
Starting Member

19 Posts

Posted - 2009-05-31 : 04:33:19

CREATE PROC PassDatabaseName
@DatabaseName VARCHAR(50)
AS
DECLARE @Stmt NVARCHAR(2000)
SET @Stmt=' USE '+@DatabaseName+' SELECT [Name] FROM SYS.OBJECTS WHERE TYPE=''U'''
PRINT @Stmt
EXEC sp_executesql @Stmt
GO
EXEC PassDatabaseName 'MASTER'

dsarath
Starting Member

3 Posts

Posted - 2009-06-02 : 07:28:07

Hii Ramesh..Thanks For u r reply.
But Iam not asking about Table names in a database.
i want table level changes(not database level like create,alter,invoke...),like insert,update,delete etc..
Please read the question and help me.
Thank u

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-06-02 : 09:43:09

This will create a script for you that will add auditing columns and triggers to database tables:
http://sqlblindman.pastebin.com/f2884dde9

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-06-02 : 09:44:40

...or this to add full archiving (inserts/update/deletes):
http://sqlblindman.pastebin.com/m334d52e5
This has the advantage of not requiring any modification of your production tables, but also requires more space.

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

dsarath
Starting Member

3 Posts

Posted - 2009-06-03 : 04:34:17

Hi Bruce Lindman.
Thanks for ur reply..the script is running fine..but how can i see the table modification data..iam new to sqlserver..can u just tell me how to use that script.
Thank You

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2009-06-03 : 09:31:20

Which script did you use?

________________________________________________
If it is not practically useful, then it is practically useless.
________________________________________________

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources