View Dependences in SQL

In Recent one of my project I need to change the column data type. At the same time I need to change the all object the table of column dependences. We find best solution best for SQL SERVER 2008 onwards this is known as View Dependences.

Step -1:

Create sample table.

CREATE TABLE STUDENT

(

ID INT IDENTITY(1,1) PRIMARY KEY,

STUDENTNAME VARCHAR(100) NOT NULL ,

LIVINGLOCATION VARCHAR(100) NULL

)

Step-2:

Create some sample procedures (or) views (or) functions (or) triggers.

CREATE PROCEDURE Get_Student

AS

BEGIN

SELECT * FROM STUDENT

END

-----------------------------

CREATE PROCEDURE Get_StudentBy_ID

(@ID INT=NULL )

AS

BEGIN

SELECT * FROM STUDENT WHERE ID=@ID

END

-----------------------------

CREATE VIEW V_GetStudent

AS

SELECT * FROM STUDENT

Step-3:

Right click on the table in Object Explorer.

Click on View Dependencies. Object Dependencies window will be opened.

In above Object Dependencies window we have seen 2 options.

1.     Objects that depend on Student table – means Objects that depends up on student table that may be Procedures, Functions, Views, Trigger...Etc.

2.     Objects on which Student depends.