How to find where the column or table is used in database

For enhancement request and applic ation issues you may want to find out where all the existing table,column is used in the database.

For example if I am  a removing a  column from Employee table I will also have to check where all this column is used so that I can update the required stored procedure based on that.

The below code will help to find out  the column name,table being used  in any user defined stored procedure,functions,triggers, views etc. You can simply run this code against the target database and get the details.


SELECT
DISTINCT OBJ.NAME,OBJ.type
FROM SYSCOMMENTS COM
INNER JOIN SYSOBJECTS OBJ
ON COM.ID=OBJ.ID
WHERE COM.TEXT LIKE '%%'

For example I have just search for hiredate column in the adventure database and below is  the output of the query.

SELECT
DISTINCT OBJ.NAME,OBJ.type
FROM SYSCOMMENTS COM
INNER JOIN SYSOBJECTS OBJ
ON COM.ID=OBJ.ID
WHERE COM.TEXT LIKE '%HireDate%'