Count number of rows in all tables..

Question

Count number of rows in all tables..

coolakhil4u6

Valued Member

Points: 63
More actions
December 5, 2010 at 8:11 pm

#243599

Hi, I am planning to do a ssis package that retrieves the total number of rows in all tables over a database. I know how to do this on a SSMS. I want to write a SSIS package for this.
Let be me more clear.
Lets say, I have 4 tables in my database.
TableName No.Of Rows
Tbl 1 100
Tbl 2 150
Tbl 3 1
Tbl 4 50
So I should execute a package which retrieves me the rowcount.
Any idea where to start?
Thanks in advance

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

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

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 1

Hi

As you have mentioned that you know how to do this in SSMS, the same query you can use in a script task or Sql task in your SSIS.

Thanks & Regards,
MC

coolakhil4u6 Valued Member Points: 63 More actions · Answer 2

If I just use the SQL Query straight out what is the use of using SSIS??? so I want to do it SSIS totally... any ideas

only4mithunc SSCrazy Eights Points: 8434 More actions · Answer 3

That is up to you what you are going to do with this result, if you are not doing any thing then as you told you don't have to use SSIS just use SSMS.

I'm not sure what you are going to do in your SSIS package... but if you want to get the total number of rows in each table, in SSIS my method is use the code in Execute SQL task ,or Script task.

Thanks & Regards,
MC

Koen Verbeeck SSC Guru Points: 259197 More actions · Answer 4

coolakhil4u6 (12/5/2010)
If I just use the SQL Query straight out what is the use of using SSIS??? so I want to do it SSIS totally... any ideas

That is like trying to tighten a screw with a hammer.

SSIS is (mostly) made for ETL purposes, aka moving data around.

SSMS is made for doing stuff with the database engine, for example executing T-SQL. And the best method of finding the row counts for all tables is using T-SQL. You can execute T-SQL in SSIS, so I don't see the problem.

(if you really want do to it the hard way - aka the not performant way - in SSIS, use a for each loop container and loop over the tables. For each table, execute some dynamic SQL that counts the rows. Store the results in a variable.)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Jeff Moden SSC Guru Points: 1004641 More actions · Answer 5

coolakhil4u6 (12/5/2010)
so I want to do it SSIS totally...

I don't even know how to spell "SSIS" so I can't help there. I am curious, though... why do you want to do it "totally" in SSIS? And, no... I'm not trying to be a smart guy about this. I'd honestly like to know. Thanks for your time.

--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)