It's quite often in real
time require to search for a particular word or text that used in stored procedure and function .
There are couples of way getting dependency objects in sql server. But, this article follows bit different approach getting
dependencies of the objects especially when a table name or column is formatted in a string
variable, for example ,see the below procedure where table name and column
names are embedded in a string variable, In this situation we can't get the
information of the object where
table "Tab_Empolyee" is
used in sql server not even with the built in feature "View Dependencies".
CREATE PROCEDURE GetEmpolyeeDetails
AS
/* Modification
History
---- Modifiedon ChangesDescription
2014-12-31 New Procedure created
*/
BEGIN
DECLARE @SqlString VARCHAR(200)
SET @sqlString='select EmpID,EmployeeName from
Tab_Employee'
EXEC(@sqlString);
END
Here, I would like to
know list of procedures/Objects that are
using table name "Tab_Employee"
, for that I written a small query that accepts a key word or text to search
and returns the list of objects where that key word is used.
DECLARE @String varchar(50)='Tab_Employees'
DECLARE @Used TABLE (name VARCHAR(1000),Line VARCHAR(7000))
SELECT @String=REPLACE(REPLACE(REPLACE(@String,'%','[%]'),'_','[_]'),'-','[-]')
INSERT INTO @Used
SELECT DISTINCT
name,text FROM sysobjects o join syscomments c ON c.id = o.id
WHERE text LIKE '%' + @String + '%'
UNION
SELECT 'ParentObject: '+Parent.name+ ' for: '+o.name,text FROM sysobjects o join syscomments c ON c.id = o.id
JOIN syscolumns depend ON
depend.cdefault=o.id
JOIN sysobjects Parent ON
Parent.id=depend.id
WHERE text LIKE '%' + @String + '%'
SELECT * from @Used
from the above query it is giving the result where field 'name' represents object name like 'storedproc
or func.
Well
you can also find out words or phrases that used in comments
section.