MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Iron Speed
Go Back   Dev Shed Forums > Databases > MS SQL Development
Reload this Page

Count All Rows In All Tables In Db

Discuss Count All Rows In All Tables In Db in the MS SQL Development forum on Dev Shed.
Count All Rows In All Tables In Db MS SQL Development forum discussing administration, MS SQL queries, and other MS SQL-related topics. SQL Server is Microsoft's enterprise database engine.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
  #1  
Old March 22nd, 2004, 11:20 AM
tjbyrne tjbyrne is offline
PLEASE HELP
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2004
Posts: 11 tjbyrne User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Question Count All Rows In All Tables In Db
Hi everyone this is my first post (ever on the net, haven’t really used forums before). Well my problem is simple (well not to me but to someone out there)

I need to count the number or rows in every table in a Microsoft SQL database. Displaying something like

Table No. of rows
table1 125
table2 654
table3 5757
table4 455
etc...

Any help would be really appreciated

Thanks everyone :-)
Reply With Quote
  #2  
Old March 23rd, 2004, 12:04 PM
sypher sypher is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jan 2004
Location: Montreal
Posts: 92 sypher User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 15 m 10 sec
Reputation Power: 5
with Entreprise Manager, there is a tab where you all see the Info but if you need it in SQL
try this:

EXEC sp_spaceused '<your table>'
and you can see the number of row,DB space used,index space used /per table
(you can do a loop do get all the tables you need, or just call sp_spaceused X times)
Reply With Quote
  #3  
Old March 24th, 2004, 03:30 AM
tjbyrne tjbyrne is offline
PLEASE HELP
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2004
Posts: 11 tjbyrne User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Arrow
Hey Thanks for the reply,
still have a problem, I need to be able to call count() on all tables but don’t want to hard code in all the table names, really what I want is to “select count(*) from * ” this doesn’t work obviously but is there some way of getting all the table names and then do a loop passing each table name, or something,
Again guys oh and girls THANKS
Reply With Quote
  #4  
Old March 24th, 2004, 05:39 AM
upscene upscene is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Dec 2003
Posts: 223 upscene User rank is Corporal (100 - 500 Reputation Level)upscene User rank is Corporal (100 - 500 Reputation Level)upscene User rank is Corporal (100 - 500 Reputation Level)upscene User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 7 h 14 m 50 sec
Reputation Power: 8
This will return a single-row resultset for each table in your database:

exec sp_MSforeachtable 'select count(*) as nr_of_rows, ''?'' as table_name from ?'
__________________
Martijn Tonies
Database Workbench: developer IDE for Firebird, MySQL, InterBase, MSSQL Server and Oracle
Upscene Productions
http://www.upscene.com
Reply With Quote
  #5  
Old March 24th, 2004, 06:26 AM
tjbyrne tjbyrne is offline
PLEASE HELP
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2004
Posts: 11 tjbyrne User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Smile
HEY THANK YOU SO SO SO MUCH, Works perfectly!!! have been working on it for ages, using temp tables, variable, cursers etc. tried everywhing..... Thanks Again!!!!
Reply With Quote
  #6  
Old March 24th, 2004, 06:36 PM
sypher sypher is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jan 2004
Location: Montreal
Posts: 92 sypher User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 15 m 10 sec
Reputation Power: 5
upscene...which version of SQL has your sp_MSforeachtable? cuz I don't have it and I got SQL2000...no SP thought...

tjbyme:
if you want to have all the table in your DB try this

select name
from sysobject
where (type='u')

this will get you all the tables and what you need is a loop to call the sp_spaceused wiht the table name
Reply With Quote
  #7  
Old March 25th, 2004, 02:22 AM
upscene upscene is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Dec 2003
Posts: 223 upscene User rank is Corporal (100 - 500 Reputation Level)upscene User rank is Corporal (100 - 500 Reputation Level)upscene User rank is Corporal (100 - 500 Reputation Level)upscene User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 7 h 14 m 50 sec
Reputation Power: 8
Using MSSQL 2000 - it's in the "master" database for a standard install.
Reply With Quote
  #8  
Old March 25th, 2004, 03:51 AM
tjbyrne tjbyrne is offline
PLEASE HELP
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Mar 2004
Posts: 11 tjbyrne User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Question
Hey Sypher
the
Code:
exec sp_MSforeachtable
worked really well but I actually tried using u'r idea at 1st, do you mean use a cursor when you say "loop" if so i cant get this to work cause i cant pass a variable into a the FROM @VARIABLE it only takes table names (no variables) Do u know any way around this. for future ref.


Code:
create table temp_tab
(
   tab_name varchar(30),
   no_of_rows INTEGER,
)


DECLARE  
	curREVIEW

CURSOR FOR
	select name 
	from sysobjects  
	where xtype = 'U'


	DECLARE @cur_tab_name varchar(30)

	OPEN curREVIEW

	FETCH curREVIEW INTO @cur_tab_name	

	WHILE (@@FETCH_STATUS =0)
	BEGIN
		DECLARE @count integer

		select @count = count(*) from @cur_tab_name
		INSERT INTO temp_tab
				    (@cur_tab_name, @count)

		FETCH curREVIEW INTO @cur_tab_name	

	END

	CLOSE curREVIEW

	DEALLOCATE curREVIEW
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > MS SQL Development > Count All Rows In All Tables In Db

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 1 hosted by Hostway.