Find all references Of Object Table , Stored Procedures, Scalar function In sql Server

Find all references Of Object Table , Stored Procedures, Scalar function In sql Server

Following is the query by which you can get all references of object  Like Table , Stored procedures ,Scalar function ..etc In Scalar function ,Trigger ,View ,stored procedure ..etc.

Many times we require that find all stored procedures that reference a given table .

By Following to search all objects in a database containing a certain string .

SELECT DISTINCT o.name AS ObjectName,
CASE o.xtype
WHEN 'C' THEN 'CHECK constraint'
WHEN 'D' THEN 'Default or DEFAULT constraint'
WHEN 'F' THEN 'FOREIGN KEY constraint'
WHEN 'FN' THEN 'Scalar function'
WHEN 'IF' THEN 'In-lined table-function'
WHEN 'K' THEN 'PRIMARY KEY or UNIQUE constraint'
WHEN 'L' THEN 'Log'
WHEN 'P' THEN 'Stored procedure'
WHEN 'R' THEN 'Rule'
WHEN 'RF' THEN 'Replication filter stored procedure'
WHEN 'S' THEN 'System table'
WHEN 'TF' THEN 'Table function'
WHEN 'TR' THEN 'Trigger'
WHEN 'U' THEN 'User table'
WHEN 'V' THEN 'View'
WHEN 'X' THEN 'Extended stored procedure'
ELSE o.xtype
END AS ObjectType,
ISNULL( p.Name, '[db]') AS Location
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
LEFT JOIN sysobjects p ON o.Parent_obj=p.id
WHERE c.text LIKE '%any text to search for%'
ORDER BY Location, ObjectName

If pass table object name then it show all references table in Stored procedures ,Scalar function ..etc .

If pass Stored procedure object name then it show all references Stored procedures in Stored Procedures ..etc .

Related Other posts

Html Code in Blodspot Radiobuttonlist Example Image In Datalist

Database backup in sql Server CheckboxList example Datetime format in gridview

Highest second record in sql Nested gridview Checkboxs in gridview