Determine the correct flow of stored procedures

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Determine the correct flow of stored procedures

Author

Topic

Rahul Raj
Starting Member

41 Posts

Posted - 2015-03-12 : 22:57:53

Hi,

We have some pre-defined stored procedures(around 4-5) which deletes/ truncates the tables. Can someone please let me know the below :

- Is there a way I can know the correct order of running the SP.May be I should check that child tables are deleted first and then the parent tables
- How can I test the same. Since I am not aware of the flow thereby should I try running with NO eligible records for deletion. Will this ensure that I have sufficient privileges for the SP or do I need to delete a record (may b an older record).

Thanks for you help!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-13 : 08:47:45

can't really answer without knowing your database schema. Certainly, if there are FK relationships, you need to delete rows from the child tables first.

Privileges are an entirely different matter, unrelated to your table structure.

Rahul Raj
Starting Member

41 Posts

Posted - 2015-03-14 : 23:26:16

Hi gbritton,

Thanks for your reply.

I analyzed that one stored procedure calls the other 4 stored procedures. As the SP's are pre-defined so I assume that it is in correct sequence. I have tested executing the SP by picking a random test record.

I would like to know is there any way I can check which all tables are being deleted by the stored procedures. I have checked the dependencies using the below query :
SELECT o.name, o.type_desc, p.name, p.type_desc
FROM sys.sql_dependencies d
INNER JOIN sys.objects o
ON d.object_id = o.object_id
INNER JOIN sys.objects p
ON d.referenced_major_id = p.object_id

but if I compare it with "view dependencies" in object explorer ; some of the tables which have Schema Bound dependencies are not appearing. e.g Table A is deleted by the SP which has a schema bound dependency on Table B, then Table B is not appearing in the SQL result.

Rahul Raj
Starting Member

41 Posts

Posted - 2015-03-15 : 21:22:36

Hi,

Can someone please sugget.

Thanks in Advance!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources