While the loop is repeating through the databases

Question

I was wondering if someone could help me create a while loop to iterate over multiple databases to retrieve data from a single table of two columns. it has been done by me so far. nothing works because I don’t know how to get the select statement to work through each database regarding the table I query 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 

Answer 1

I would consider sp_MSForEachDB , which is much simpler ...

Edit:

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

Answer 2

 CREATE TABLE #T (dbname sysname NOT NULL PRIMARY KEY, SumPGCOUNT INT, CREATED DATETIME) DECLARE @Script NVARCHAR(MAX) = '' SELECT @Script = @Script + ' USE ' + QUOTENAME(name) + ' IF EXISTS(SELECT * FROM sys.tables WHERE OBJECT_ID=OBJECT_ID(''dbo.tbldoc'')) INSERT INTO #T SELECT db_name() AS dbname, SUM(PGCOUNT) AS SumPGCOUNT, CREATED FROM dbo.tbldoc GROUP BY CREATED; ' FROM sys.databases WHERE state=0 AND user_access=0 and has_dbaccess(name) = 1 AND [name] like 'z%' and create_date between '2010-10-17' and '2011-01-15' ORDER BY [name] IF (@@ROWCOUNT > 0) BEGIN --PRINT @Script EXEC (@Script) SELECT * FROM #T END DROP TABLE #T 

Answer 3

My code to search for data from more than one database will look like this:

 use [master] go if object_id('tempdb.dbo.#database') is not null drop TABLE #database go create TABLE #database(id INT identity primary key, name sysname) go set nocount on insert into #database(name) select name from sys.databases where name like '%tgsdb%' --CHANGE HERE THE FILTERING RULE FOR YOUR DATABASES! and source_database_id is null order by name Select * from #database declare @id INT, @cnt INT, @sql NVARCHAR(max), @currentDb sysname; select @id = 1, @cnt = max(id) from #database while @id <= @cnt BEGIN select @currentDb = name from #database where id = @id set @sql = 'select Column1, Column2 from ' + @currentDb + '.dbo.Table1' print @sql exec (@sql); print '--------------------------------------------------------------------------' set @id = @id + 1; END go 

Answer 4

 DECLARE @Loop int DECLARE @MaxLoop int DECLARE @DBName varchar(300) DECLARE @SQL varchar(max) SET @Loop = 1 SET @DBName = '' set nocount on SET @MaxLoop = (select count([name]) FROM sys.databases where [name] like 'Z%') WHILE @Loop <= @MaxLoop BEGIN SET @DBName = (select TableWithRowsNumbers.name from (select ROW_NUMBER() OVER (ORDER by [name]) as Row,[name] FROM sys.databases where [name] like 'Z%' ) TableWithRowsNumbers where Row = @Loop) SET @SQL = 'USE [' + @DBName + ']' exec (@SQL) ... ... set @Loop = @Loop + 1 END 

*** Note: I have not added verification if exists here.

Answer 5

In the end, I wrote one last week on the fly for some of the things I did.

Blog post:

http://tsells.wordpress.com/2012/02/14/sql-server-database-iterator/

Here is the code.

 SET NOCOUNT ON GO use master go Declare @dbname nvarchar(500), @variable1 int, @variable2 int, @variable3 int, @totaldb int = 0, @totaldbonserver int = 0, @totaldbwithmatches int = 0 -- Get non system databases Declare mycursor CURSOR for select name, database_id from SYS.databases where database_id > 4 order by name desc open mycursor fetch next from mycursor into @dbname, @variable1 while (@@FETCH_STATUS <> -1) BEGIN DECLARE @ParmDefinition NVARCHAR(500) Declare @mysql nvarchar(500) = 'select @variable2OUT = COUNT(*) from [' + @dbname + '].INFORMATION_SCHEMA.TABLES where Upper(TABLE_NAME) like ''MyTable'''; SET @ParmDefinition = N'@variable2OUT int OUTPUT' set @totaldbonserver = @totaldbonserver + 1 Execute sp_executesql @mysql, @ParmDefinition, @variable2 OUTPUT if @variable2 = 1 BEGIN DECLARE @ParmDefinition2 NVARCHAR(500) Declare @mysql2 nvarchar(500) = 'select @variable2OUT = COUNT(*) from [' + @dbname + '].dbo.MyTable'; SET @ParmDefinition2 = N'@variable2OUT int OUTPUT' Execute sp_executesql @mysql2, @ParmDefinition2, @variable3 OUTPUT set @totaldb = @totaldb + 1 if @variable3 > 1 BEGIN Print @dbname + ' matched the criteria' set @totaldbwithmatches = @totaldbwithmatches + 1 END ELSE Select 1 END fetch next from mycursor into @dbname, @variable1 END PRINT 'Total databases on server: ' Print @totaldbonserver PRINT 'Total databases tested () : ' Print @totaldb PRINT 'Total databases with matches: ' Print @totaldbwithmatches CLOSE mycursor DEALLOCATE mycursor 

Answer 6

It does not use a loop. Hope this helps!

Note that "TABLE_OWNER" matches the "SCHEMA Owner" and "TABLE_TYPE" will determine if the item is a table or view.

 --This will return all tables, table owners and table types for all database(s) that are NOT 'Offline' --Offline database information will not appear Declare @temp_table table( DB_NAME varchar(max), TABLE_OWNER varchar(max), TABLE_NAME varchar(max), TABLE_TYPE varchar(max), REMARKS varchar(max) ) INSERT INTO @temp_table (DB_NAME, TABLE_OWNER, TABLE_NAME, TABLE_TYPE,REMARKS) EXECUTE master.sys.sp_MSforeachdb 'USE [?]; EXEC sp_tables' SELECT DB_NAME, TABLE_OWNER, TABLE_NAME, TABLE_TYPE FROM @temp_table --Uncomment below if you are seaching for 1 database --WHERE DB_NAME = '<Enter specific DB Name>' --For all databases other than 'System Databases' WHERE DB_NAME not in ('master','model','msdn','tempdb') order by 1,2,3 

Answer 7

You do not need to use the "USE DATABASE" statement. You can choose from a specific database table using an identifier of 3 parts:

select * from MyDatabase.dbo.MyTable