SQL server table population source

Question

Hi,

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

Something is polulating it, with table sizes data (which makes sense as it is Audit database).

The SQL server has too many jobs.

I want to know what is populating the audit tables.

Is there anything like sys.comments etc? which can tell me what is populating tables or do I have to check the code inside each job?

Regards

Manjot

Answer 1

most likely it is being populated by triggers onteh the audited tables.

Answer 2

If you know what causes data to go into the audit table, you can run a (very) brief Profiler session against the database, filtering specifically on that table, while triggering the action. That will give you further steps to back-trace the root action.

Answer 3

Try looking at msdb..sysjobsteps in the command column for the destination table names; this will only work if they are using T-SQL to populate the tables. If they're using an SSIS (or DTS) package, this won't work.

Answer 4

you could 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