Running T-SQL statement on all Databases

Question

Running T-SQL statement on all Databases

bkirk

SSCrazy

Points: 2824
More actions
December 14, 2009 at 6:09 pm

#207540

I have about 50 individual client databases on my SQL server and want to run a quick report of Total Sales $ per database client for the past month of November 2009.
Each database has an Orders Table with an OrdersTotal & OrderStatus column.
I want to group by Database Name and sum by OrdersTotal where the OrderStatus is Complete.
This is an easy enough SQL Select Query, but can someone tell me how to run this dynamically for all databases?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

This is not a complete solution since there is no table DDL, and column details and I am not advanced level developer ( I am a DBA)

Construct a Temp Table and Insert / Update that table by looping through the databases, using dynamic SQL Statements.

I guess it can be also done using CTE or Cursors.

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

To go along with what Bru said, if you want folks to give you a coded answer, please see the first link in my signature below to help you help them give you the answer you're looking for.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

NickDBA Hall of Fame Points: 3684 More actions · Answer 3

You may want to check out the undocummented procedure sp_msforeachdb

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

Yeah... that should do it. I just hate using it because it will, in fact, do it for all the DB's and that's not usually what you want.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

NickDBA Hall of Fame Points: 3684 More actions · Answer 5

Bkirk....Jeff is bang on the money there, if you choose to use that undocummented sp it'll run for EVERY database, including system ones.

Without showing us the table defs etc it's difficult to give you a propper solution. What im posting here is a "stab in the dark" based on the little info available, but you should be able to modify accordingly.

if isnull(object_id('tempdb..#OrdersTotals'),'')=''

CREATE TABLE #OrdersTotals (ClientDB varchar(100), OrdersTotal INT)

INSERT INTO #OrdersTotals

EXECUTE sp_msforeachdb 'USE ?

IF DB_NAME() NOT IN(''master'',''msdb'',''tempdb'',''model'')

SELECT

DB_NAME() AS ClientDB,

SUM(OrdersTotal) AS OrdersTotal

FROM Orders

WHERE [YourDateColumn] BETWEEN ''01 NOV 2009'' AND ''30 NOV 2009''

AND OrderStatus = ''Complete'''

DROP TABLE #OrdersTotals'

bkirk SSCrazy Points: 2824 More actions · Answer 6

Apologies to all. I need to get better at the proper definitions and schemas in my post. Will do so in future.

As for the last post that is spot on. I did some more google research on the suggested system SP and it looks like it will do the job i am after.

Thanks and much appreciated.