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
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.
[...] SQL SERVER – Find Table in Every Database of SQL Server – Part 2 [...]
[...] SQL SERVER – Find Table in Every Database of SQL Server – Part 2 [...]
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
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/05/13/simulating-undocumented-procedures.aspx
the ” incorrect syntax near 1 ” could also be caused by a database name that begins with the number 1.
[…] Find Table in Every Database of SQL Server […]