Our forums are currently in maintenance mode and the ability to post is disabled. We will be back up and running as soon as possible. Thanks for your patience!

General discussion


Auditing changes in all SQL tables

By paulhenriques ·
I have an application that we purchased that accesses it's database in SQL Server 2000. I wanted to know what tables the application accesses when a certain function is performed.

I thought that I could add a trigger to each table that would insert a flag into a new table but there are almost 700 tables in the database and this would take forever to do.

Is there something else I can do to identify what tables are updated/inserted when I perform a function in an application?

Any help is much appreciated.


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

One By one it would, however

by Tony Hopkinson In reply to Auditing changes in all S ...

Declare cTables Cursor for select * from systables where [type] = 'U'
Declare @TableName varchar(64)
Declare @Cmd Varchar(1023)
open cTables
Fetch from ctables into @Tablename
While @@Fetch_status = 0
Select @cmd = 'Create Trigger For Update On ' + @TableName + '......'
Exec (@cmd)
Fetch next from ctables into @TableName
close cTables
Deallocate Ctables

Messy but it will work, haven't tested it so may be a syntax error or two

Take a back up, execcise the function, save your data and restore.

or you could use the above template and fill in @cmd with Drop Trigger...

a wee tip , to see whats going to happen replace exec with print and specify a tablename in the cursor select where clause

Collapse -

SQL Profiler

by ceswanson In reply to Auditing changes in all S ...

A free tool comes with SQL Server 2000 called SQL Profiler. Start it up from the start menu or from the tools menu of Enterprise Manager. Start a new trace connecting to the server that you want to monitor. The SQLStandardProfiler template should be good to get you started. If you have an active server, the data will be overwhelming. But using pause, and filters, you should be able to limit the data captured to just the database you want. This tool shows you in great detail what is happened, and MS Support has helped me troubleshoot my servers with it before.

Collapse -

audit tools

by eric.huang In reply to Auditing changes in all S ...

Your best bet is to buy a product that will perform data audits. Products from Lumigent or Idera will perform a scan on the transaction logs and data files to collect transactions and logged them to a database.

I have seen other alternative solutions where you can duplicate your table schemas to another database with added flags to indicate who changed it, when, and the type of action. You can then loop through the system tables to get the tables and columns to automatically generate your trigger scripts.

I have tried tools that generate triggers for table audits and tools such as Lumigent's. Thus far, we found that non-trigger tools are more efficient and has very minimum affect on your server's performance. Just make sure you have proper backups to make the tool work.

Hope this will help you get started.


Collapse -

The answer...kinda

by chapman.tim In reply to Auditing changes in all S ...

Hi Paul,
I am not 100% I understand your problem, but I think I can give a guesstimate that might help.
There are a few options for you. First, if you know what stored procedures are being called, you can run queries against system tables to see what tables the stored procedures depend on. Based on that information you can compile your list to check. Second, you could write TSQL procedures that generate the script to create triggers on every table in your system. This might be a pain to do, but it may be what you are looking for. Third, you could run SQL Profiler and trace what procedures/batches are being called in your system. You can save this list to a file or table. If you save to a table, you can run queries against it, find the procedures and then run queries against the system tables to find your dependency list. Let me know if you have any more questions.

Related Discussions

Related Forums