1

I have the following query that needs to be executed in different databases but table name and its structure is the same.

--query to be executed
select 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 [database_01].[dbo].[custaddr] 
--- from [database_02].[dbo].[custaddr] 
--- from [database_03].[dbo].[custaddr] 
order by credit_hold_reason asc

As you see, I just need to change the name of the database or "loop" in some sort of variable that holds the name of the databases, i.e.,

tables_in_loop = ['[database_01].[dbo].[custaddr]', 'from [database_02].[dbo].[custaddr]', 'from [database_03].[dbo].[custaddr]']

When you have the name of the databases, a connection is generated to them and all these databases are in the same server.

I do not have any approach or scratch code about this because this involves more depth in knowledge in SQL and unfortunately I am not an expert - yet. I read the following post SQL Server: how to get a database name as a parameter in a stored procedure and the approach to solve it seems similar here:

declare @dbname nvarchar(255);    
set @dbname = 'db1';    

if @dbname = 'db1'
    use db1;
else if @dbname = 'db2'
    use db2;

but @dbname needs to increased to the next database and this is not specified in the link.

Any help or code in here would be appreciated.

Improve this question
9
  • When you open a DB connection, you connect to a specific database. Will you be connecting to a different database than the query is run against? In other words, when you execute from [database_02].[dbo].[custaddr] will you connection be connected to database_02? Or will you be connected to for example databaseAAA and query against database_02? Please update the question to clarify. Commented May 15, 2018 at 18:59
  • all on the same server? Commented May 15, 2018 at 18:59
  • @bvmcode same server. Commented May 15, 2018 at 19:00
  • @AaronLS, good observation. Let me update my question. Commented May 15, 2018 at 19:00
  • 1
    In my business case, we have thousands of databases with the same tables in them (for historical reasons). How to loop through them is a legitimate question since building unions for thousands of dbs is somewhat unmanageable. The same need exists for running updates or delete statements (I realize there are 3rd party tools for this as well). Commented May 15, 2018 at 19:58

3 Answers 3

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

I wonder if there is any other way except forming up SQL Script / dynamic SQL ? Though it seems there is none
1

I think you just need a union 

select 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 [database_01].[dbo].[custaddr] 
union all
select 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 [database_02].[dbo].[custaddr] 
union all
select 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 [database_03].[dbo].[custaddr]  
order by credit_hold_reason asc

TLDR the comments. You want something other than union.

Improve this answer

5 Comments

Isn't there like an approach that avoids you to use union all?
Doubt it. Dynamic sql in a loop is not going to be pretty. And will be multiple result sets. Select into a temp use space. Copy paste is pretty darn easy.
See answer from jschmitter. Told you it would not be pretty.
I already marked jschmitter as my solution. Not pretty but it solved my problem, however, as I read in this other post stackoverflow.com/questions/3943823/… dynamic SQL should not be used at all because of the security holes :(
From a performance perspective, this is the best solution. If you'd stick it in an indexed view it will perform like a charm
1

I asked "In other words, when you execute from [database_02].[dbo].[custaddr] will you connection be connected to database_02?"

You responded:

when I execute [database_02].[dbo].[custaddr] then my query will run against database_02 and execute [database_03].[dbo].[custaddr] then my query will run against database_03 – Alejandro BR

So I take this to mean two things:

1) You will be connected to the database that you are running the query under.
2) You want to run these queries seperately. In other words you aren't trying to combine the results into a single result set across the databases.

In other words, you are not doing cross database queries. I notice you didn't use the term "connection" in your response though, so I'm beginning to think this is a concept you are missing.

The solution is simple, use two part names instead of 3 part names:

from [dbo].[custaddr]

This ensures that as you change your connection to different databases, then the query will run against that database.

In the context of an application, it is alot easier to do dynamically build the connection string or make it configuration driven with multiple connection strings in a data structure.

If you start building queries dynamically for such a simple use case then you are over complicating things quite a bit. There's a number of cons to dynamic queries that are beyond this question but it's easy to find lots of information on these.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.