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

No comments:

Post a comment