SQL Server - Checking Where an Object is used

As databases grow in size and complexity, often there are dead objects that are left behind. By dead, I mean these objects are no longer being used, but everyone is afraid to drop them for fear of breaking something. SQL Server provides a simple way to determine where an object is being used so you can decide whether it is needed or not. SQL Server will tell you whether your object is being used in a function or stored procedure or a view etc. it obviously can't tell you whether another application is referencing such an object. So, before you delete an object if the query below doesn't return anything, further research may be necessary.

The script below will accept the object name and list all other objects that are using/referencing this object.

    --Declare a variable or alternatively you could create 
    --a stored procedure and pass this as a parameter.
    DECLARE @objectName nvarchar(4000)
    --name of your object.
   SET @ObjectName = 'MyObject'
   SELECT
        S.NAME,
        C.Text

    FROM SysObjects S
    INNER JOIN SysComments C
        ON S.ID = C.ID
    WHERE 
        C.Text LIKE '%' + @ObjectName + '%'

For example, if I run the above script in Northwind database to check where "Categories" table is being used, I get the following result.

Happy Coding!

Thank you.