CODEWITHSUNDEEP
sqlsql-server
Jeff
Jeff

Reputation: 882

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

Upvotes: 25

Views: 103175

Answers (7)

Marian
Marian

Reputation: 892

My code to search for data from more than one database would be:

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

Upvotes: 4

gbn
gbn

Reputation: 432261

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

Edit:

EXEC sp_MSForEachDB 'USE [?]; IF DB_NAME() LIKE ''Z%%''
BEGIN


END
'

Upvotes: 53

Jeremy F.
Jeremy F.

Reputation: 1868

This doesn't use a loop. Hope this helps!

Note that "TABLE_OWNER" is that same as "SCHEMA Owner" and "TABLE_TYPE" will identify 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

Upvotes: 1

Shai Alon
Shai Alon

Reputation: 990

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 didn't add the check if exists here.

Upvotes: 3

tsells
tsells

Reputation: 2771

I ended up writing one last week on the fly for some stuff I was doing.

Blog post here:

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

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453243

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

Upvotes: 3

jlnorsworthy
jlnorsworthy

Reputation: 3974

You don't have to use a "USE DATABASE" statement. You can select from the particular database table by using a 3 part identifier as in:

select * from MyDatabase.dbo.MyTable

Upvotes: -2

Related Questions