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.

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%'"
FROM #TableNameResults
DROP TABLE #TableNameResults

Reference : Pinal Dave (https://blog.sqlauthority.com), Simon Worth

SQL Scripts, undocumented SQL
Previous Post
SQL SERVER – 2000 – SQL SERVER – Delete Duplicate Records – Rows – Readers Contribution
Next Post
SQL SERVER 2005 – FIX Error: 18456 : VISTA Windows Authentication

Related Posts

8 Comments. Leave new

  • Good tips. Keep up the good work.

  • Vinod Joseph
    May 15, 2008 11:37 am

    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

  • Sathish Kumar. K
    May 15, 2008 3:12 pm

    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?
    please friends.

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

  • As ms_foreachdb is undocumented, I prefer doing

  • SigueSigueSputnik
    December 17, 2012 11:42 pm

    the ” incorrect syntax near 1 ” could also be caused by a database name that begins with the number 1.

  • Channdeep Singh
    May 7, 2015 10:40 pm

    Thanks a lot sir.


Leave a Reply