Determine Which Objects Reference a Table in SQL Server

Question

I need to rename a table that has many columns and stored procedures that process against that table. How can one get all Items in database that have a relation to a table in such a scenario?

Answer 1

Using sys.dm_sql_referencing_entities:

SELECT 
    referencing_schema_name, referencing_entity_name, referencing_id, 
    referencing_class_desc, is_caller_dependent
FROM 
    sys.dm_sql_referencing_entities ('mySchemaName.myTableName', 'OBJECT');
GO

where 'mySchemaName.myTableName' is your schema.table, for example 'dbo.MyTable'

Answer 2

If you need to find database objects (e.g. tables, columns, triggers) by name - have a look at the FREE Red-Gate tool called SQL Search which does this - it searches your entire database for any kind of string(s).

It's a great must-have tool for any DBA or database developer - did I already mention it's absolutely FREE to use for any kind of use??