Data populating with Audit table

Question

Data populating with Audit table

mcfarlandparkway

SSCertifiable

Points: 7699
More actions
June 28, 2016 at 10:03 am

#327906

Hello,
I have an PerosnAudit table, Not sure from where this table data is populating.
And also we have person table,In both these tables columns are same.the extra column I see in personAudit table as Audit ID.
We checked triggers and I see one trigger created under Person table called delPersons on dbo.Person for delete as ..begin bla bla bla......
We queried this one to check are there any stored procedures are using for this PersonAudit table, but I don't see this table in any of the stored procedures.and didn't find in packages too or batch files..
SELECT DISTINCT
SP_Name = O.name,
Table_Name = OO.name
FROM sys.sysdepends D INNER JOIN sys.sysobjects O ON
O.id = D.id
INNER JOIN sys.sysobjects OO ON
OO.id = D.depid
WHERE O.xtype = 'P'
Any idea like anywhere do I need to check it?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246991 More actions · Answer 1

SQL Search[/url] is free and really useful when you are looking for references to database objects. Maybe it will help you track this down.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Eric M Russell SSC Guru Points: 125562 More actions · Answer 2

There is a function called object_definition() that accepts object_id and then returns the T-SQL of object like stored procedures, UDF, or views. When combined with sp_msforeachdb, you can leverage it to quickly scan across all objects in all databases.

exec sp_msforeachdb

'

print ''?'';

use ?;

select db_name(), type, name as object_name

, object_definition(object_id) as object_text

from sys.objects as o

where is_ms_shipped = 0

and (object_definition(object_id) like ''%PersonAudit%'')

';

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

mcfarlandparkway SSCertifiable Points: 7699 More actions · Answer 3

Thank you Eric, But didn't came nay results.

showing object name as delPersons ....I see only this.

Is there any way that they are handling this audit data from some where I mean from application or from scripts?

Phil Parkin SSC Guru Points: 246991 More actions · Answer 4

mcfarlandparkway (6/28/2016)
Thank you Eric, But didn't came nay results.
showing object name as delPersons ....I see only this.
Is there any way that they are handling this audit data from some where I mean from application or from scripts?

Yes there is. The application which maintains the Person table could also be maintaining the audit table.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel