Jeff Jeff - 1 month ago 11
SQL Question

While Loop to Iterate through Databases

I was wondering if someone could help me with creating a while loop to iterate through several databases to obtain data from one table from two columns. this is was I have done so far. nothing works because i do not know how to make the select statement work through each database with regards to the table that I am querying from each database (dbo.tbldoc)

DECLARE @Loop int
DECLARE @DBName varchar(300)
DECLARE @SQL varchar(max)
DECLARE @tableName VARCHAR(255)

SET @Loop = 1
SET @DBName = ''

WHILE @Loop = 1
BEGIN

SELECT [name] FROM sys.databases
WHERE [name] like 'z%' and create_date between '2010-10-17' and '2011-01-15'
ORDER BY [name]

SET @Loop = @@ROWCOUNT

IF @Loop = 0
BREAK

SET @SQL = ('USE ['+ @DBNAME +']')
IF EXISTS(SELECT [name] FROM sys.tables WHERE name != 'dbo.tbldoc' )
BEGIN
SELECT SUM(PGCOUNT), CREATED FROM **dbo.tbldoc**
END
ELSE
--BEGIN
PRINT 'ErrorLog'
END

gbn gbn
Answer

I would consider sp_MSForEachDB which is a lot easier...

Edit:

sp_MSForEachDB 'IF DB_NAME LIKE ''Z%%''
BEGIN


END
'
Comments