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



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

,
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.
    Thanks.

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

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

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

    Reply
  • As ms_foreachdb is undocumented, I prefer doing

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

    Reply
  • Channdeep Singh
    May 7, 2015 10:40 pm

    Thanks a lot sir.

    Reply

Leave a Reply

Menu