(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!

Okay,
I've gotten the Loop working..
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

(4 row(s) affected)
BSCColla002_SITE

(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..

Fran (fxomeara (AT) yahoo (DOT) com) writes:
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 (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

On Apr 10, 3:28 pm, Erland Sommarskog <esq... (AT) sommarskog (DOT) se> wrote:
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.