Home > Link, Query, SQL > BISQL # 94 – How to find tables which is not participating in Stored procedure table relationship

BISQL # 94 – How to find tables which is not participating in Stored procedure table relationship

Hi Folks ,

Continuing from my last post ,In which we are finding various relationship with table and stored procedure

BISQL # 93 – How to find list of Stored procedure which does not depend on any table operation : In this post we have learn how to find stored procedure which is not participating and Stored procedure table relationship

Also to be in synch each other my one of 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 tables which is not participating in Stored procedure table relationship

    Lets do some querying to get expected result first on Adventure work DB

    Following query will provide us list of all tables in given Database system

    SELECT * FROM sys.tables

    Which have following in output as 70 tables

    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 and which table have been used interns of table – Stored procedure relation 

    image_thumb5

    Now here what our problem requirement is in which list of Table which are not dependent on stored procedure.

      image

    Which means only 6 tables are used by stored procedure !

    We can clearly see n this output and output compare to just above script stored procedure does not contain any table in its definition which are in second output result.

    Hope you will like this post on How to find tables which is not participating in Stored procedure table relationship.

    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. sushant pawar
      May 5, 2012 at 12:04 pm | #1

      I did live demonstration of dis on Bse’s database……..sqlmentalist becoming a mentor for me im in love wid this

    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,114 other followers

    %d bloggers like this: