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
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
SET @SQL = ('USE ['+ @DBNAME +']')
IF EXISTS(SELECT [name] FROM sys.tables WHERE name != 'dbo.tbldoc' )
SELECT SUM(PGCOUNT), CREATED FROM **dbo.tbldoc**
I would consider sp_MSForEachDB which is a lot easier...
sp_MSForEachDB 'IF DB_NAME LIKE ''Z%%'' BEGIN END '