Posted by Erland Sommarskog on 04/10/07 21:28

Fran (fxomeara@yahoo.com) writes:
> Now if I add:
> USE @DBs
> SELECT * FROM sys.tables WHERE type = 'U'
>
> I get:
> Incorrect syntax near '@DBs'.

You need to use dynamic SQL to change the database dynamically. However,
rather than inventing your own wheel, you could use sp_MSforeachdb. This
stored procedure is undocumented and unsupported, but nevertheless useful.
Here is an example:

sp_MSforeachdb 'if ''?'' like ''aba%'' select name from [?].sys.tables
where type = ''U'''

The ? is a placeholder for the database name. For full details on
parameters, you can do

sp_helptext sp_MSforeachdb.

There is also an sp_MSforeachtable.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]