41

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?

ΩmegaMan
  • 29,542
  • 12
  • 100
  • 122
Tavousi
  • 14,848
  • 18
  • 51
  • 70

3 Answers3

61

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'

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • Thanks.But what is "mySchemaName" and "OBJECT"? – Tavousi Nov 26 '12 at 11:09
  • 3
    @Tavousi - See http://msdn.microsoft.com/en-us/library/bb630351.aspx As you can see you want to use `'OBJECT'` as the second parameter. The default schema in SQL Server is `dbo` so that's probably your schema. – Tobsey Nov 26 '12 at 11:29
  • 1
    If this unexpectedly returns 0 rows for you, be aware that in SQL Server 2008-2012, sys.dm_sql_referencing_entites requires CONTROL permission on the referenced object to work. Try running the query as a database user with elevated permisisons. – Jon Schneider Mar 26 '15 at 20:34
  • 1
    This doesn't seem to work for finding objects referenced in a different database to the one where the SP is being executed – Zach Smith Oct 16 '17 at 13:19
  • I tried this on an ancient SQL 2008 (10.0) box, searching for a table and it returned two views but not the four triggers defined on the table. Should it have returned them? – user2871239 Dec 04 '19 at 08:17
17

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).

enter image description here

enter image description here

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??

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
0

if you want of ref of DB item like table, column, procedure, etc..

You can use visual-expert tools, we can analyse the code SqlServer code

more info: https://www.visual-expert.com/EN/visual-expert-documentation/code-cross-references/

Brice Vuagniaux
  • 1