Find the Dependencies of a Table in SQL Server

In this article, we will show you how to find the Dependencies of a Table or column in SQL Server using system procedure. We can find dependency using system procedure called sp_depends or we can create a script to find dependency in SQL server

Find dependency using sp_depends

       The below system procedure will help you to find depending script of your table or column. I am not suggest to use system sp to find dependency, it will not display depending script, if we used the column in dynamic query.

use DataBaseName

GO

EXEC sp_depends @objname = 'first name' ;

Script to find dependency

 Below script will help you to find dependency in entire SQL server. Create the procedure any of your database and execute with column or table name as parameter, it will output depending script from all your data base exists in  SQL server.

 EXEC  Sp_depends_sqlFeathers @objectName = 'first name'

CREATE PROC Sp_depends_sqlFeathers

(

        @objectName VARCHAR(200) = ''

)

/**********************************************************************

Createdby                   Date                        

SQLFeathers                 23-10-2009   

EXEC  Sp_depends_sqlFeathers @objectName = 'first name'

************************************************************************/

AS

BEGIN

       DECLARE @db VARCHAR(30)

       DECLARE @id INT = 1

       DECLARE @cnt INT

       DECLARE @sqlqry VARCHAR(500)

       DECLARE @tblDBs TABLE(id INT IDENTITY, db VARCHAR(30))

       IF OBJECT_ID('tempdb.dbo.#skip', 'u') is not null

              DROP TABLE #skip

       CREATE  TABLE #skip (error VARCHAR(150))

       IF OBJECT_ID('tempdb.dbo.#tbsysc', 'u') is not null

              DROP TABLE #tbsysc

       CREATE  TABLE #tbsysc

       (

              [dbname] VARCHAR(50),

              [objname] VARCHAR(200),

              [schema name] VARCHAR(50),

              [Open Query gethelp] VARCHAR(100)

       )

       INSERT INTO @tblDbs

              SELECT name FROM sys.databases

              WHERE database_id>4

       SELECT @cnt = @@rowcount

       WHILE @id<=@cnt

       BEGIN

              SELECT @db = db

              FROM @tblDBs

              WHERE id = @id

              SET  @sqlqry =  'select distinct ''' + @db

                                + ''' as DB, s.name,m.name, '''+ @db

                                +'.' +'dbo.sp_helptext ['' + m.name + '''+'.'

   +''' + s.name '+ '+'']''

                             from ' +

                               @db + '..sysobjects s with(nolock)

                             left join ' +

                               @db + '..syscomments c with(nolock)

                               on s.id = c.id

                             left join

                               sys.schemas m with(nolock)

                               on s.uid = m.schema_id

                             where text like ''%' + @objectName + '%'''

              BEGIN TRY

                      INSERT INTO #tbsysc

                      EXEC (@sqlqry)

              END TRY

              BEGIN CATCH

                      INSERT INTO #skip(error)

                      SELECT @db + ' is skipped'

              END CATCH

              SET @id += 1

       END

       SELECT * FROM #tbsysc ORDER BY 1

       SELECT * FROM #skip ORDER BY 1

END