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