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://www.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

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