Posted by Utahduck on 04/11/07 15:55

On Apr 10, 3:28 pm, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Fran (fxome...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

I might suggest a new approach... create a view that will retrieve all
of the table names. Here is a stored procedure that will build that
view. You'll need to re-create the view if you add or remove any new
databases but the view should stay good as long as your databases
remains static.

CREATE PROC sp_BuildDBTablesView

as

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.Views WHERE TABLE_NAME =
'vw_DBTables')
DROP TABLE vw_DBTables

DECLARE @CreateView varchar(8000)
SET @CreateView = 'CREATE VIEW vw_DBTables

as

'

SELECT name
INTO #DBs
FROM sysDatabases

DECLARE @DBName varchar(100)

WHILE (SELECT COUNT(*) FROM #DBs) > 0
BEGIN

SET @DBName = (SELECT min(name) FROM #DBs)

SET @CreateView = @CreateView + 'SELECT * FROM ' + @DBName +
'.INFORMATION_SCHEMA.TABLES' + CHAR(13) + 'UNION' + CHAR(13)

DELETE #DBs WHERE name = @DBName
END

SET @CreateView = LEFT(@CreateView, LEN(@CreateView) - 6) --- Take
off the last UNION

PRINT @CreateView

EXEC master..sp_SQLExec @CreateView

DROP TABLE #DBs

GO

Now, anytime you wish to view the tables just run:

SELECT * FROM master.dbo.vw_DBTables

And you'll get the entire list.

I hope that helps.

[Back to original message]