Home > Query, SQL > BISQL # 89 – How to establish / Find relationship between Table and stored Procedure in Database

BISQL # 89 – How to establish / Find relationship between Table and stored Procedure in Database

Hi Folks ,

After a few days when we complete our Database Design and Development we left with our unformatted code and Unstructured Data.

In this series I will let you know to go back make our Tables, Columns ,Procedures More readable and more easy and easy to maintain.

I am not naming this series but i will make sure to have set every time when i am going post based on this.

We will surely have consolidated list and points plus one full fledge white paper based on this series , so be patient and lets understand each point one by one

The Main aim to cover in this post is to have relationship understanding and Use between Table and Procedures in any Database

For this series we are going to use our favourite DB i.e Adventure Works DB

In this article we are covering

  • 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

Now lets understand one by one each of the point in detail

How to analyse all schema

Schema are most important and connective entity in Database

Following script to find all Schema from current database

SELECT * FROM sys.schemas

Result of script as follows

image

How to analyse all Tables in Database 

Tables are most IMP and highly used entity in our Database

Following script to find all Tables from current database

SELECT * FROM sys.tables

Result of script as follows

image

How to analyse all procedure

Stored Procedure are driving blokes of any Database System

Following script to find all Stored Procedure from current database

SELECT * FROM sys.procedures

Result of script as follows

image

How to analyse all dependency Between Table and Stored Procedure

Dependency Table is not use in General , Its is Completed created and maintain by system Itself

We are going to make use of it to achieve our main goal .

In this table we will find the relation between tables and procedure in Complex format

Following script to find all Dependency Between Table and Stored Procedure from current database

SELECT * FROM sys.sysdepends

Result of script as follows

image

Establishing Relation Between  Table and Stored Procedure

Now lets try to establish relation among table and procedure

Following is diagram to understand relationship among them

image 

Final and Quick result script for Relationship of Table and Stored Procedure

Now its very easy to join and get Desired result

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;

image

Now this set is more useful and more understandable !!

In Upcoming post in Same series we will find how efficiently we can use this to solve our many problem.

Hope you will like this post on finding relation among table and procedure.

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

Advertisements
Categories: Query, SQL
  1. July 3, 2012 at 2:00 pm

    Ah! I take a teensy downtime from the blogging atmosphere and come back
    to find that you have turned awesome?! Wow, times have changed!
    Keep up the far out work!

  1. April 8, 2012 at 9:53 am

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

%d bloggers like this: