query to find the tables in a db and their row count

Question

Post reply

query to find the tables in a db and their row count

santhosh.edukulla

SSC Rookie

Points: 37
More actions
August 21, 2008 at 8:40 am

#382347

Hi

Output for the query below doesnt seem to produce any records.

Can any body please help.This query isto find the tables in a db and their row count in sql 2k5

Thanks!

Declare @table varchar(255),

@temprowcount varchar(255),

@query nvarchar(555),

@query2 nvarchar(555)

create table #temp(namet varchar(255),countf varchar(255))

--Declare

DECLARE rowcursor CURSOR FOR

SELECT So.name

from sys.objects so

where so.type = 'U'

OPEN rowcursor

FETCH NEXT FROM rowcursor INTO @table

IF @@FETCH_STATUS <> 0

PRINT ' '

WHILE @@FETCH_STATUS = 0

BEGIN

set @query = N'select '+ @temprowcount+' = count(*) from '+ @table;

Exec (@query)

set @query2 = 'Insert #temp values('+@table+ ','+@temprowcount +')'

Exec (@query2)

FETCH NEXT FROM rowcursor INTO @table

END

select * from #temp

drop table #temp

CLOSE rowcursor

DEALLOCATE rowcursor

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

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

Joel Ewald SSCertifiable Points: 6120 More actions · Answer 1

@temprowcount is null and as such concatenating anything to it will yeild null (if ansi nulls is enabled)

In esence both of your query string variables will always be null.

[Code]

DECLARE @temprowcount varchar(255), @table varchar(255), @query varchar(1000)

SET @table = 'TestTable'

set @query = N'select '+ @temprowcount+' = count(*) from '+ @table;

select @Query

set @query = N'select @temprowcount = count(*) from '+ @table;

select @Query

[/Code]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

This query/view provides that information and much more as well: http://qa.sqlservercentral.com/scripts/tables/62545/

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 3

rbarryyoung beat me to it, but I'll post this anyway. It does what you want, although not as useful as his view.



set nocount on

create table #temp(namet varchar(255),countf varchar(255))

exec sp_MSforeachtable 'insert #temp select ''?'', count(1) from ? with (nolock)'

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

Pradyothana Shastry SSCertifiable Points: 5211 More actions · Answer 4

Pradyothana Shastry

SSCertifiable

Points: 5211

December 31, 2009 at 4:33 am

#1097826

Below query will return the row count of user tables in a db.

SELECT o.name AS "Table Name", i.rowcnt AS "Row Count"

FROM sysobjects o, sysindexes i

WHERE i.id = o.id AND indid IN(0,1)

AND xtype = 'u'

AND o.name <> 'sysdiagrams'

ORDER BY i.rowcnt DESC

--COMPUTE SUM(i.rowcnt)

GO

- Pradyothana http://www.msqlserver.com

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

Pradyothana Shastry (12/31/2009)

Below query will return the row count of user tables in a db.

SELECT o.name AS "Table Name", i.rowcnt AS "Row Count"

FROM sysobjects o, sysindexes i

WHERE i.id = o.id AND indid IN(0,1)

AND xtype = 'u'

AND o.name <> 'sysdiagrams'

ORDER BY i.rowcnt DESC

--COMPUTE SUM(i.rowcnt)

GO

That can be quite inaccurate unless you use DBCC UPDATEUSAGE first.

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