October 3, 2006 at 6:16 am
Good afternoon folks,
I was wodering if someone could help me out with an issue I have. I need to add some auditing triggers to tables in one of the databases I administer. This is quite a big application with numerous nested stored procedures that update numerous tables.
Rather than just auditing the current table and logging the current details(old and new values) which I can successfully do, I need to identify the parent procedure i.e. the first procedure that was called from the web form...
I was trying to use the SP_ID but could genereate object_ids from the sysprocesses table... Would anyone be able to direct me how to do this both efficently and effectively.
All help is greatly appreciated.
Thanks
M
October 4, 2006 at 8:50 am
I had similar problem - I needed to investigate dependencies between tables. I exported all code (SP, triggers, views) into text file and wrote a procedure in MS Access, VBA macro that scans text file and creates a table with columns:
procedure name
dependant object
access type (for tables: from, join, update, ..; for SP exec only)
I can run a MS access query on this table. From this table, you just need to pull procedures they are not listed as dependant objects:
Select distinct procedure_name
From allObjects
Where procedure_name not In (select distinct dependant_object From allObjects)
Instead of scanning code, you can try to create the same table by sp_depends procedure. However this gives you two sets of results so eventually you might need to play with the SP code.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply