BISQL # 93 – How to find list of Stored procedure which does not depend on any table operation
Hi Friends ,
Continuing from my last series of Code reviewing..
- BISQL # 90 – How to find Number of lines of Code in procedure or function
Reviewing and Updating in Existing Database with very less amount of time is really challenging task for all architects. Its not rocket science we have evolved from whatever we have in terms of resource , Knowledge and Research.We must always find way which will solve our current problem and save future time.Saving future time is most important aspect.
Now this all conversation i have shared because from my one of post in which i have explain how can Tables, Columns ,Procedures More readable and more easy and easy to maintain.
On my previous article (How to establish / Find relationship between Table and stored Procedure in Database) in which we have already covered following points :
In this article we are going to cover following problem statement
Problem : How to find stored procedure which is not participating and Stored procedure table relationship
Lets do some querying to get expected result first on Adventure work DB
Following query will provide us Stored procedure in given system
SELECT * FROM Sys.procedures
Which have following in output as 9 stored procedure
Now lets fire query which we have already discuss : Final and Quick result script for Relationship of Table and Stored Procedure
SELECT DISTINCT p.name AS 'Stored Procedure' , s.name AS 'Schema Name', t.name AS 'Table Name' FROM sys.sysdepends AS d INNER JOIN sys.procedures AS p ON d.id = p.object_id INNER JOIN sys.tables AS t ON t.object_id = d.depid INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id;
Which shows output as in which stored procedure which table have been used
Now here what our problem requirement is in which stored procedure we not using any of table
So following is Key query which answer to our problem statement
SELECT name FROM sys.procedures WHERE object_id NOT IN ( SELECT DISTINCT p.object_id FROM sys.sysdepends d INNER JOIN sys.procedures p ON d.id = p.object_id INNER JOIN sys.tables t ON t.object_id = d.depid INNER JOIN sys.schemas s ON t.schema_id = s.schema_id )
Which in output will provide list of SP which is not dependent on any table
We can clearly see this output and output compare to just above script stored procedure uspPrintError does not contain any table in its definition
Hope you will like this post on How to find list of Stored procedure which does not depend on any table operation.
If you really like reading my blog and understood at lest few thing then please don’t forget to subscribe my blog .
If you wan daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog
Where todays links are
For More information related to BI World visit my Mentalist Blog
Link Resource Blog >> Daily Interesting links
SQL Server Mentalist >> SQL Learning Blog
Business Intelligence Mentalist >> BI World
Connect With me on
| Facebook |Twitter | LinkedIn| Google+ | Word Press | RSS |
SQL Server Mentalist 