How to: Find a Table on a SQL Server Across all Databases

Some times it is difficult to find a table name from a SQL instance that hosted tremendous database.  “Which database I suppose to search for?” is the question that often pop-up in DBA’s mind and they hope the user can give them the database name as well.  Sadly speaking in most cases user do not know which database their application connect to.

To find a table in the SQL across all databases you can use undocumented stored procedure sp_MSForEachDB.

sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''%your_table_name%'''