Home > Link, Query, SQL > BISQL # 93 – How to find list of Stored procedure which does not depend on any table operation

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 :

  • How to analyse all schema
  • How to analyse all Tables in Database 
  • How to analyse all procedure
  • How to analyse all dependency Between Table and Stored Procedure
  • Establishing Relation Between  Table and Stored Procedure
  • Final and Quick result script for Relationship of Table and Stored Procedure
  • 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

    image 

    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

    image

    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

    image

    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

    Link Resource Website

    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 |

    About these ads
    Categories: Link, Query, SQL
    1. No comments yet.
    1. No trackbacks yet.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    Follow

    Get every new post delivered to your Inbox.

    Join 1,120 other followers

    %d bloggers like this: