Query database object Dependencies

USE TestDB1
GO
Select distinct OBJECT_NAME (object_id) as objname
FROM sys. SQL _dependencies
WHERE referenced_major_id = OBJECT_ID ('dbo. tblproject ')
Order by objname

Although the above Code can be used from sys. SQL _dependencies directly searches for the reference relationship of the SQL object, but this method can only find the direct reference relationship in a single database, cross-database reference and reference generated by dynamic SQL are not found in this way. However, you can use the text search method to traverse the syscomments table of each data.
Run the following code to find the stored procedure using the tblProjects table in the TestDB1 and TestDB2 databases:

DECLARE @ txt varchar (100)
SELECT @ TXT = 'tblproject'
DECLARE @ SQL VARCHAR (MAX)
SELECT @ SQL = ISNULL (@ SQL + 'Union all', '') +
+ 'Select OBJ. Name, OBJ. Type ,'''
+ [Name]
+ '''As DBName from'
+ [Name]
+ '.. Sysobjects obj with (NOLOCK) INNER join'
+ [Name]
+ '.. Syscomments cmt with (NOLOCK) on obj. id = CMT. id WHERE (OBJ. type = 'P' or obj. type = ''v'') and cmt. text LIKE ''%'
+ @ TXT
+ '% '''
FROM Master. sys. SysDatabases WITH (NOLOCK)
WHERE Name IN ('testdb1', 'testdbdb ')
EXEC (@ SQL)