Determine Which Objects Reference a Table in SQL Server
I work with SQL Server 2008 and I have a database that has more than 1500 columns and about 500 stored procedures and ... .
I want to rename a table that has several relations and is referenced in many stored procedure and views and ... .
How to I can get all Items in database that has a relation with this table?
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??
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'
It is an another solution I found. You do not have to install any tools. Just run on query analyzer.
Use [Database] Go SELECT referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID), referencing_object_name = o.name, referencing_object_type_desc = o.type_desc, referenced_schema_name, referenced_object_name = referenced_entity_name, referenced_object_type_desc = o1.type_desc, referenced_server_name, referenced_database_name --,sed.* -- Uncomment for all the columns FROM sys.sql_expression_dependencies sed INNER JOIN sys.objects o ON sed.referencing_id = o.[object_id] LEFT OUTER JOIN sys.objects o1 ON sed.referenced_id = o1.[object_id] WHERE referenced_entity_name = 'SP_Pay_GetData' order by referencing_object_name