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
