Retrieve ALL tables from ALL Databases

[Fran]

(SQL 2005)
I'm looking to create a stored procedure to first "select name from
sys.databases where name like '%site'" then pass each name to the
following using some kind of loop "USE @name select name from
sys.tables where type = 'U'"
I tried a while statement, but the master sys.databases recordset
doesn't change..

It's the loop I can't get to work.

Any help is greatly appreciated!

[Fran]

Re: Retrieve ALL tables from ALL Databases

Okay,
I've gotten the Loop working..

>>Code

INSERT INTO t_fo_databases
SELECT name FROM sys.databases WHERE name like '%SITE' ORDER BY name

DECLARE @DBs as varchar(MAX)
WHILE (SELECT TOP 1 names FROM t_fo_databases) LIKE '%SITE'

BEGIN
SET @DBs = (SELECT TOP 1 names FROM t_fo_databases)
PRINT @DBs
USE [master]
DELETE FROM t_fo_Databases WHERE names = @DBs
END

>>Returns

(4 row(s) affected)
BSCColla002_SIT E

(1 row(s) affected)
BSCColla1_SITE

(1 row(s) affected)
BSCITPor1_SITE

(1 row(s) affected)
sps2007t1_SITE

(1 row(s) affected)


Now if I add:
USE @DBs
SELECT * FROM sys.tables WHERE type = 'U'

I get:
Incorrect syntax near '@DBs'.

Any thoughts?
Thank you..

[Erland Sommarskog]

Re: Retrieve ALL tables from ALL Databases

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_MSforeachtab le.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.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

[Utahduck@hotmail.com]

Re: Retrieve ALL tables from ALL Databases

On Apr 10, 3:28 pm, Erland Sommarskog <esq...@sommars kog.sewrote:

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_MSforeachtab le.
>
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
>
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/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_BuildDBTable sView

as

IF EXISTS (SELECT * FROM INFORMATION_SCH EMA.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_S CHEMA.TABLES' + CHAR(13) + 'UNION' + CHAR(13)

DELETE #DBs WHERE name = @DBName
END

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

PRINT @CreateView

EXEC master..sp_SQLE xec @CreateView

DROP TABLE #DBs

GO

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

SELECT * FROM master.dbo.vw_D BTables

And you'll get the entire list.

I hope that helps.