SQL SERVER – Find Table in Every Database of SQL Server – Part 2 Extension

Long time blog reader and SQL Server Expert Simon Worth has suggested two additional method to achieve same results as described in article SQL SERVER – Find Table in Every Database of SQL Server. Both of his methods rely on sp_msforeachdb.

Method 1

sp_msforeachdb "SELECT '?' DatabaseName, Name FROM ?.sys.Tables WHERE Name LIKE '%address%'"



Method 2

CREATE TABLE #TableNameResults (DatabaseName VARCHAR(100) NOT NULL, TableName VARCHAR(100) NOT NULL)
INSERT INTO #TableNameResults
EXEC sp_msforeachdb "SELECT '?' DatabaseName, Name FROM ?.sys.Tables WHERE Name LIKE '%address%'"
SELECT *
FROM #TableNameResults
DROP TABLE #TableNameResults

What to Watch When You Find Table in Every Database

Both methods are short and easy to remember, which is why I like them for a quick search. Keep in mind that sp_msforeachdb is undocumented. Microsoft does not support it, and it can change or behave differently between versions. For a one time search on your own server that is fine. For a job that runs every night, I prefer a loop I control.

A few tips that make these scripts more reliable:

• Put brackets around the placeholder, like [?].sys.tables. A database name with a space or a dash breaks the query otherwise.
• Offline or restoring databases cause errors. If you build your own loop, read sys.databases and keep only the rows where state_desc = 'ONLINE'.
• Size the temp table columns as sysname instead of VARCHAR(100), because object names can be up to 128 characters long.
• The LIKE pattern '%address%' matches any table with that word anywhere in its name. Use an exact name with = when you know it.

If you write your own loop, build the query with QUOTENAME(name) for each database, run it with sp_executesql, and insert the results into one temp table. It takes a few more lines, but you know exactly which databases were searched.

The same pattern works for more than tables. Point it at sys.columns to find every database with a column of a given name, or at sys.procedures to find where a stored procedure lives. You only change the view in the query and the name in the LIKE.

One more note: the search can only read databases your login can access. If a database is missing from the results or shows an error, check your rights on it before you decide the table does not exist. A quick test on a database you know well confirms the script before you trust it on all of them.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.