Find Dependency In SQL Server

Why?

Let’s discuss one situation. There were so many tables in database and I have to change only one column of the table then how can I found this column is how many times used in any views or store procedure or any other database object. There is a ways to find this type of situation. First right click on the table and then click on dependency it will shows all the object that use this table but if I can see only that particular alter table column then there is no way to do that. After that problem I have found one solution that mention bellowed.

Description

First create one table valued function CheckDependancy in your database.That function script define belowed.

   1: set ANSI_NULLS ON

   2: set QUOTED_IDENTIFIER ON

   3: go

   4:  

   5:  

   6: Create function [dbo].[CheckDependancy](@dependanceobjectname Varchar(100))

   7: RETURNS @DependanceTable TABLE (

   8: [name] varchar(200),

   9: [type] varchar(100)

  10: )

  11: AS

  12: BEGIN

  13:  

  14: INSERT @DependanceTable

  15:  

  16: select distinct temp.name1,temp.TypeOf from 

  17: (

  18:     SELECT (case Obj.[type] 

  19:         when 'FN' then 'Function'

  20:         when 'P' then 'Procedure'

  21:         when 'TR' then 'Trigger'

  22:         when 'TF' then 'Function'

  23:         when 'V' then 'View'

  24:         else 'None' end) AS TypeOf,obj.name AS name1,syscom.[text] from sys.objects As obj

  25:                 INNER JOIN syscomments As syscom ON obj.object_id = syscom.id

  26:     WHERE obj.[type] in ('FN','V','TR','P','TF')

  27:         AND syscom.[text] like '%' + @dependanceobjectname + '%' 

  28: ) As temp Order By TypeOf

  29:  

  30: return

  31: END

  32:  

After Creating this function fire a query like

   1: Select * from CheckDependancy('companyname')

This query will show all the view,store procedure or function name that use the companyname column. 🙂