2

I need to identify a table that is mentioned anywhere in database (in stored proc, views, and, etc.). I tried to find a query online, but couldn't find it. Any help would be great!

NonProgrammer
  • 1,337
  • 2
  • 23
  • 53
  • 2
    Not sure why your question got downvoted. It seems to me to totally on topic and something that people need to find now and then. – Sean Lange Aug 28 '14 at 18:33
  • 1
    You might find a query-based solution [here ...](http://stackoverflow.com/questions/1584107/sql-server-2005-search-views-for-certain-database-objects) – AHiggins Aug 28 '14 at 18:41
  • 1
    Or [here](https://sqlblog.org/blogs/aaron_bertrand/archive/2011/10/06/a-handy-search-procedure.aspx). – Aaron Bertrand Aug 28 '14 at 18:54

2 Answers2

3

I use the free SQL Search plugin for MS Management Studio for things like that: http://www.red-gate.com/products/sql-development/sql-search/

Dave.Gugg
  • 6,561
  • 3
  • 24
  • 43
BI Dude
  • 1,842
  • 5
  • 37
  • 67
2

I often use this snippet when I'm looking for dependencies. In this case, you would replace the text with what you're searching (assuming you're on MS SQL Server):

USE [DBNAME]

SELECT OBJECT_NAME(id)
FROM syscomments
WHERE [text] LIKE '%enter_search_here%'
GROUP BY OBJECT_NAME(id)

You can also look for specific object types by adding a check for object property:

    WHERE OBJECTPROPERTY(id, 'IsTable') = 1

Here is a LIST of useful object properties!

  • 2
    Two problems with `syscomments`: (1) it's a backward-compatibility view that has been deprecated and should not be used (use `sys.sql_modules` instead), and (2) it breaks procedures > 4000 characters across multiple rows, so there is a chance it could miss procedures because your search term fell on the boundary. [See this post](https://sqlblog.org/blogs/aaron_bertrand/archive/2011/10/06/a-handy-search-procedure.aspx). – Aaron Bertrand Aug 28 '14 at 18:53