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. 🙂

Advertisements

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