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
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
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
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
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
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;
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
SQL Server Mentalist 
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!