SQL Server table population source

Question

I have an audit database (created by someone else).

Something is processing it, with table size data (which makes sense, since this is an audit database).

There are too many jobs in the SQL server.

I want to know what populates audit tables.

Is there something like sys.com etc.? who can tell me what populates the tables, or do I need to check the code inside each job?

Hi

Manjot

Answer 1

Try looking at msdb..sysjobsteps in the command column for destination table names; this will only work if they use T-SQL to populate the tables. If they use the SSIS (or DTS) package, this will not work.

Answer 2

you can try running something like this:

SELECT DISTINCT o.name,o.type_desc FROM sys.sql_modules m INNER JOIN sys.objects o ON m.object_id=o.object_id WHERE m.definition Like '%YourTableName%' ORDER BY 2,1 

EDIT after OP mentioned SQL Server 2000

this should work on SQl Server 2000:

 --remove comments to see the actual text too SELECT DISTINCT o.name --,c1.colid,c1.text FROM sysobjects o INNER JOIN syscomments c1 ON o.id = c1.id --join to next section of code in case search value is split over two rows LEFT OUTER JOIN syscomments c2 ON o.id = c2.id AND c2.colid=c1.colid+1 WHERE c1.text Like '%YourTableName%' OR RIGHT(c1.text,100)+LEFT(c2.text,100) Like '%YourTableName%' ORDER BY 1--,2 

Answer 3

most likely, it is populated with onteh triggers of checked tables.

Answer 4

If you know what causes the data to fall into the audit table, you can start a (very) brief Profiler session on the database by filtering this particular table when the action starts. This will give you further steps to track root actions.