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

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.

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

Reference : Pinal Dave (http://blog.SQLAuthority.com), Simon Worth

About these ads

9 thoughts on “SQL SERVER – Find Table in Every Database of SQL Server – Part 2

  1. Hi, i noticed an error when running the above on sql 2005:

    Msg 102, Level 15, State 1, Line 1
    Incorrect syntax near ‘.1′.

    (1 row(s) affected)

    (1 row(s) affected)

    Any idea what could be going wrong?

    Thanks & Regards,
    Vinod Joseph

  2. Hi to all,
    i am developer for the past two year,
    i want to gain knowledge for next level. i don’t have time to go for an institue also here, no such institute will teach sql server perfectly.

    Can any one suggest basic to advance sql learning videos?
    download?
    please friends.

  3. Pingback: Journey to SQL Authority with Pinal Dave

  4. Pingback: SQL SERVER - Find Table in Every Database of SQL Server - Part 3 Journey to SQL Authority with Pinal Dave

  5. Hey Vinod,

    The error you are getting is being cause by the single quote being a `. Make sure all sure that all the `s are single quotes.

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #028 | SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s