Help with a loop to collect database names and tables within those databases.

I am trying to create a loop that will go though all the databases and
collect all the table names.
Currently the loop goes around and around till it scans all the
databases but only pull from the current database you are in.
I am probably missing something that is obvious.
DECLARE @.ExecSQLcmd VARCHAR(2048) -- Creates Storage Space for SQL
Command
DECLARE @.DBNum_to_Name INT -- Creates Storage Space for
Database Name
CREATE TABLE #tmp_RebuildIndexesPrameters (
DatabaseName NVARCHAR(128),
TableName CHAR(255),
DailyFragAmt INT, -- Maximum daily fragmentation to allow (30
is default)
DailyMaxRebuidAmt INT, -- Maximum index size durring the w
to do a rebuild instead of defrag (100 Default)
WlyFragAmt INT, -- Maximum wly fragmentation to allow (5
is default)
WlyMaxRebuidAmt INT, -- Maximum index size durring the
wend to do a rebuild instead of defrag (100 Default)
LastRun DATETIME,
Priority INT, -- User Defined varable. (-1 Never Run; 1-999,
Priority with 1 being the highest.)
Completed INT, -- Required to prevent looping based upon
Priority and LastRun.
)
/*********************************
Loop though all DBs by DBID Number
Notes: The collection of Database
names and Table names should take
place durring this loop. Once the
data has been collected, it can be
compared to the existing paramerers
**********************************/
SELECT @.DBNum_to_Name = min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > 4
WHILE @.DBNum_to_Name is not null
BEGIN
SELECT @.ExecSQLcmd='use ' + name + ' exec dbo.sp_UD_RebuildIndexes '
FROM master.dbo.sysdatabases
WHERE dbid = @.DBNum_to_Name
Print (@.ExecSQLcmd) -- For Debugging
SELECT @.DBNum_to_Name =min(dbid)
FROM master.dbo.sysdatabases
WHERE dbid > @.DBNum_to_Name AND DATABASEPROPERTY(name, 'IsReadOnly') =
0
INSERT INTO #tmp_RebuildIndexesPrameters (DatabaseName, TableName)
(SELECT TABLE_CATALOG, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES)
END
SELECT * FROM #tmp_RebuildIndexesPrameters -- For debugging
DROP TABLE #tmp_RebuildIndexesPrameters -- Delete the temporary
table
Thanks
-Matt_Try this:
USE master
GO
CREATE TABLE #TableNames (
DatabaseName sysname,
TableName sysname
)
DECLARE @.dbName sysname,
@.vcCommand varchar(4000)
DECLARE curDB CURSOR READ_ONLY FORWARD_ONLY
FOR
SELECT [name]
FROM master.dbo.sysdatabases
WHERE [name] NOT IN ('master', 'model', 'tempdb', 'msdb', 'distribution',
'Northwind', 'pubs') -- skip these db's
OPEN curDB
FETCH NEXT
FROM curDB
INTO @.dbName
WHILE @.@.FETCH_STATUS = 0
BEGIN
SET @.vcCommand = 'INSERT #TableNames SELECT ''' + @.dbName + ''', TABLE_NAME
FROM ' + @.dbName + '.INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME'
EXECUTE (@.vcCommand)
FETCH NEXT
FROM curDB
INTO @.dbName
END
CLOSE curDB
DEALLOCATE curDB
SELECT *
FROM #TableNames
DROP TABLE #TableNames|||It works, but I am not too familiar with the cursor command. Would you
mind if you could explain it step by step? Also this needs to run on
SQL 2000 as well as 2005, and so far it looks like it does that.
Thanks
-Matt-|||"Matthew" <MKruer@.gmail.com> wrote in message
news:1138393397.822646.317470@.z14g2000cwz.googlegroups.com...
> It works, but I am not too familiar with the cursor command. Would you
> mind if you could explain it step by step? Also this needs to run on
> SQL 2000 as well as 2005, and so far it looks like it does that.
> Thanks
> -Matt-
>
For help with cursors, go read through the books online.
In a nutshell, the code opens a cursor (recordset in programming parlance).
Grabs the first row
In a loop creates the SQL statement I gave you.
Executes the sql statement which loads info into the temp table
grabs the next row
Loops
Closes the cursor
selects data from the temp table.
Rick Sawtell
MCT, MCSD, MCDBA|||Thanks, I was just looking at it at BOL, but like most everything they
don't say it is layman's terms too well.
One additional question if you don't mind.
I need to add in null values to the other fields. DailyFragAmt,
DailyMaxRebuidAmt, WlyFragAmt, Priority, LastRun etc...
How would you recommend I do that? Process the query first and then
insert, or do it both at the same time.