Total Rows - 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

General SQL Server Forums

New to SQL Server Programming

Total Rows

Author

Topic

DJJUBES
Starting Member

4 Posts

Posted - 2012-05-20 : 14:59:32

I need to show the total amount of rows in a specific table?
The query is as follows:
As part of the planning process to expand the database that supports Northwind
operations, the IT manager would like to know how many rows are currently in specific
tables so that he can conduct capacity planning.
The results needed include two columns, TableName( containing all the tables in the database and Rows, which contain the total amount of all the rows per table)

Any advice would be much appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-20 : 15:46:13

easiest way is this


EXEC sp_Msforeachtable 'select ''?'',count(*) from ?'

create a table to populate result and use

INSERT table
EXEC.... (above EXEC code here)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

prett
Posting Yak Master

212 Posts

Posted - 2012-05-24 : 00:59:40

Query to find number Rows, Columns, ByteSize for each table in the current database:

USE DatabaseName
GO
CREATE TABLE #temp (
table_name sysname ,
row_count INT,
reserved_size VARCHAR(50),
data_size VARCHAR(50),
index_size VARCHAR(50),
unused_size VARCHAR(50))
SET NOCOUNT ON
INSERT #temp
EXEC sp_msforeachtable 'sp_spaceused ''?'''
SELECT a.table_name,
a.row_count,
COUNT(*) AS col_count,
a.data_size
FROM #temp a
INNER JOIN information_schema.columns b
ON a.table_name collate database_default
= b.table_name collate database_default
GROUP BY a.table_name, a.row_count, a.data_size
ORDER BY CAST(REPLACE(a.data_size, ' KB', '') AS integer) DESC
DROP TABLE #temp

Ref: http://blog.sqlauthority.com/2007/01/10/sql-server-query-to-find-number-rows-columns-bytesize-for-each-table-in-the-current-database-find-biggest-table-in-database/

mani_12345
Starting Member

35 Posts

Posted - 2012-05-24 : 01:25:41

select object_name(sysindexes.id) "Database Name" ,rows from sysindexes

try this u wll get a list of all database and number of rows and explore accrdngly which u want.

i hope u got ur ans??

granuharmot
Starting Member

31 Posts

Posted - 2014-09-23 : 05:14:22

unspammed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources