script help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

script help

Author

Topic

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-11-23 : 12:50:55

Hello guys,

I have multiple databases with same table structures,
I need an output with database name, sum of NumTran between given two date range.

How do we get this kind of output?

declare @tbl1 table
(
TranDate date,
NumTran int
)

INSERT INTO @tbl1
SELECT '01/10/2009', 10

INSERT INTO @tbl1
SELECT '02/11/2009', 200

INSERT INTO @tbl1
SELECT '03/12/2009', 450

select SUM(NumTran) as SumTran from @tbl1 where TranDate between '01/01/2009' and '03/30/2009'

DatabaseName,NumTranCount
Abc001 660
Abc002
Abc003
Abc004

X002548
Not Just a Number

15586 Posts

Posted - 2010-11-23 : 15:20:53

Not quite sure what you're looking for

SELECT * FROM sys.databases

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-11-23 : 15:42:56

Yes i know all the databases name are stored on sys.databases, the main problem is i used the culsor i have more than thusands dbs so it is taking long time (each db has also millions records)

anybody has good solutions pls.

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-23 : 15:52:25

You want to search through a thousand of databases, each with the same design, and query the same tables in each of those dbs, where each table has millions of records?

Why do you have a thousand of databases that are the same?

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-11-23 : 15:57:05

yes, because it is using one application for all customers, each customer has own db, but all are similar structure.

TimSman
Posting Yak Master

127 Posts

Posted - 2010-11-23 : 16:02:26

I don't know about performance, but I guess you could use a WHILE loop, build a dynamic query with the db name, and dump the results into a table, then after it's all said and done, query the results.

But I'm thinking that if you want to query every single database like that, you'd want to run it as a scheduled job when traffic is light.

rudba
Constraint Violating Yak Guru

415 Posts

Posted - 2010-11-23 : 16:07:44

i tried to uxd z shild loop and dump the result, the performance is really bad can't do this....
we can not query every single db coz the new db is creating by application any time user is entring new job.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources