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
8 Comments. Leave new
Good tips. Keep up the good work.
Thanks.
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
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.
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
the ” incorrect syntax near 1 ” could also be caused by a database name that begins with the number 1.
Thanks a lot sir.
your welcome Channdeep.