|
|
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.
Navigation:
[Reply to this message]
|