Get list of all database objects with table name or column name
While working on a databse, I frequently get the need of retrieivng list of all stored procedures, triggers, or function that uses a particular column or particular table. I have collected list of all such queries which will help us to find databse objects using a specific column or specific table.
To get list of all tables with a specific table name, use anyone of the below following queries
----Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%tablename%'
----Option 2
SELECT DISTINCT o.name, o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%tablename%'
To get list of all procedures using a specific column or a specific table, any one of the above query would work. In addition, you can use the below queries to do the same
----Option 1
select [Name]
from sys.procedures
where object_definition([object_id]) like '%YourColumnName%';
----Option 2
select OBJECT_NAME(object_id)
from sys.sql_modules
where OBJECTPROPERTY(object_id,'IsProcedure') = 1 and
definition like '% YourColumnName or YourTableName %'
use the below query to get list of all objects along with its type
Select distinct sysobjects.name
, case
when sysobjects.type = 'TR' then 'TRIGGER'
when sysobjects.type = 'P' then 'PROCEDURE'
when sysobjects.type = 'V' then 'VIEW'
else 'UNKNOWN' end type
from sysobjects inner join syscomments
on sysobjects.id = syscomments.id
where syscomments.text like '% YourColumnName or YourTableName %'
All the above queries will work fine for small databses. But as the database tables increases, number of columns having the same name will also increase. In suh cases, above query will return many results out of which you have to filter out the required one manually. Below query will avoid such manual work by letting you define the column name along with the table name
DECLARE @Schema SYSNAME
DECLARE @Table SYSNAME
DECLARE @Column SYSNAME
SET @Schema = 'dbo'
SET @Table = 'TableName'
SET @Column = 'ColumnName'
SELECT o.