While Loop to Iterate Through Databases

While Loop to Iterate through Databases

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

Edit:

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

END
'

How to loop a query in different databases in T-SQL?

I like creating a temp table for the query results, creating a table variable for my list of dbs to query, and then looping through that list of databases to execute the query for each, inserting the results into the temp table.

This way you can query the results repeatedly after running the whole loop process only once.

Try this (you'll need to modify the #results temp table declaration and the where clause of the first insert statement):

--drop temp table if it exists; this clears the results each time you run the full script
if object_id('tempdb..#results') is not null
    drop table #results
go

--recreate temp table; this is used for storing the result set.  It's created as a temp table instead of a table variable so that it can be queried repeatedly after gathering results.
create table #results (
    dbName varchar(128)
    ,cust_num varchar(128)
    , [name] varchar(128)
    , credit_hold bit
    , credit_hold_reason varchar(128)
    , [Type of credit hold reason] varchar(128)
    , credit_hold_date varchar(128)
    , credit_limit int);  --adjust column declaration as needed for query results

--create a variable to track the database list
declare @dbList table (dbName varchar(128), indx int)

insert into @dbList
select dbName = name, row_number() over (order by name) 
from master.sys.databases
--where --insert your own where clause here to narrow down which databases to run the query on.

--declare variables for use in the while loop
declare @index int = 1
declare @totalDBs int = (select count(*) from @dbList)
declare @currentDB varchar(128)
declare @cmd varchar(300)
--define the command which will be used on each database.
declare @cmdTemplate varchar(300) = '
    use {dbName};
    insert into #results
    select db_name(), cust_num, name, credit_hold, credit_hold_reason, 
       (case 
           when credit_hold_reason = ''NP'' then ''No payments''
           when credit_hold_reason = ''UK'' then ''Unknown''
           when credit_hold_reason = ''BK'' then ''Bankruptcy''
        end) as ''Type of credit hold reason'',
     credit_hold_date, credit_limit
    from [custaddr] 
    order by credit_hold_reason asc
'

--loop through each database and execute the command
while @index <= @totalDBs 
begin
    set @currentDB = (select dbName from @dbList where indx = @index)
    set @cmd = replace(@cmdTemplate, '{dbName}', @currentDB)

    execute(@cmd)

    set @index += 1
end

--see the results of the command; this can be queried repeatedly by itself after it's created the first time
select *
from #results

Note that looping through a table variable is more efficient than declaring a cursor.

Loop through databases to get INFORMATION_SCHEMA.COLUMNS

For sql-server, there's 'sp_MSforeachdb'. It will loop through all databases on the server for you. Inside it, you can use dynamic sql and pump the data into a temp table created outside of it.

create table #columnDatas (
    dbase_name sysname,
    table_name sysname,
    data_type sysname,
    column_name sysname
);

exec sp_MSforeachdb '

    use     [?]

    insert  #columnDatas 
    select  ''?'', table_name, data_type, column_name
    from    information_schema.columns
    where   column_name like ''%zip%'';

';

select * from #columnDatas

UPDATE: Version utilizing sys.databases

Thank you to Sean Lange who points to issues with sp_MSforeachdb skipping databases. So here's another version, still dynamic, that loops sys.databases instead:

declare @columnDatas table (
    dbase_name sysname,
    table_name sysname,
    data_type sysname,
    column_name sysname
);

declare @template nvarchar(max) = '
    select  ''@db'', table_name, data_type, column_name
    from    [@db].information_schema.columns
    where   column_name like ''%zip%''
';

declare 
    @dbid int = 5, -- ignore system databases
    @maxDbid int = (select max(database_id) from sys.databases);

while @dbid <= @maxDbid 
begin

    declare @db sysname = (
        select  name 
        from    sys.databases 
        where   database_id = @dbid
    );

    declare @sql nvarchar(max) = replace(@template, '@db', @db);
    insert @columnDatas exec (@sql);
    set @dbid += 1;

end

select * from @columnDatas;

Syntax error in SQL Query while iterating through databases

MySQL treats the special query SHOW DATABASES basically the same as a regular SELECT query in terms of how it is sent to the querying client, and your application code can therefore treat it exactly the same way as you would handle a regular SELECT statement.

The SHOW DATABASES query returns one column called Database and a row for each database.

> show databases;
+-------------------------+
| Database                |
+-------------------------+
| information_schema      |
| db1                     |
| db2                     |
+-------------------------+
3 rows in set (0.00 sec)

So instead of the for loop utilizing count() and a single call to mysqli_fetch_array(), use the same while loop structure you would use in a SELECT query, and assign $db with it.

$res = mysqli_query($connection, "SHOW DATABASES");
if (!$res) {
  // handle error...
}
// On query success, fetch in a normal loop 
while ($d = mysqli_fetch_assoc($res)) {
  // Database name is in the column `Database`
  $db = $d['Database'];

  // Advisable to quote it with backticks...
  $sql1 = "USE `$db`";
  // etc...
}

loop through database and show in table

mysql_fetch_array fetches a single row - you typically use it in a while loop to eat all the rows in the result set, e.g.

echo "<table>";

while ($row_users = mysql_fetch_array($results)) {
    //output a row here
    echo "<tr><td>".($row_users['email'])."</td></tr>";
}

echo "</table>";

MS SQL loop through all DBs. Script works to alter table but not stored procedure

You need to nest dynamic SQL for this task because a proc CREATE or ALTER must be the first statement in the batch:

SET @sql= N'EXEC(N''USE ' + QUOTENAME(@dbname) + N';EXEC(N''''CREATE PROC...;'''')'')';

How to use while loop to iterate through each table name in SQL Server?

Here's an example of a WHILE loop. Basically, you get the first TableName, then if it's NOT NULL, you do your functions. Then get the next table name, and repeat as necessary.

DECLARE @CurrentTableName nvarchar(100)
DECLARE @CustomSQL nvarchar(4000)

SET @CurrentTableName = (SELECT TOP 1 TableName FROM @dataStructure ORDER BY TableName)

WHILE @CurrentTableName IS NOT NULL
BEGIN
    SET @CustomSQL = 'SELECT TOP 10 * FROM ' + @CurrentTableName
    EXEC (@CustomSQL)
    SET @CurrentTableName = (SELECT TOP 1 TableName FROM @dataStructure WHERE TableName > @CurrentTableName ORDER BY TableName)
END

Note that SQL commands often cannot contain variable names in key spots (e.g., SELECT * FROM @tableName). Instead, you save it as an SQL string (what I've called @CustomSQL above) and then EXEC it (put brackets around the variable name though).

Edit: Do this on a test site first before production, and know where the 'cancel query' button is. It's not often, but it's also not unknown, that the 'getting the next row' part isn't properly written and it just runs in a perpetual loop.

---

Related Topics