How to Determine the Number of Rows of Every Table ?

[Sina]

Hello every one.

According to microsoft the following script should work in 6.5

The following script will return the name and the number of rows in every user-defined table in a given database:
USE pubs -- replace pubs with your database name

SET NOCOUNT ON
DECLARE tables_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tables_cursor
DECLARE @tablename varchar(30), @quote char(1)
SELECT @quote = ""

FETCH NEXT FROM tables_cursor INTO @tablename
WHILE (@@fetch_status <> -1)
BEGIN
EXEC ('Select ' + @quote+'Rows in ' + @tablename + ' = '+
@quote + ', count(*) from '+ @tablename)
FETCH NEXT FROM tables_cursor INTO @tablename
END
DEALLOCATE tables_cursor
SET NOCOUNT OFF

But I'm getting error where the key work &quot;in&quot; is used.

Could you provide some help.

Thanks much.

ps. have to run this on 6.5

 

[TitleistDBA]

Try this. It should work.
SET NOCOUNT ON
DECLARE tables_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tables_cursor

DECLARE @tablename varchar(255),
@quote char(1)

select @quote = ' '
FETCH NEXT FROM tables_cursor INTO @tablename

WHILE (@@fetch_status <> -1)
BEGIN
select @tablename
EXEC ('Select count(*) from '+ @tablename)

FETCH NEXT FROM tables_cursor INTO @tablename
END
DEALLOCATE tables_cursor
SET NOCOUNT OFF

http://www.acushnetcompany.com

 

[JayKusch]

Here is one w/out the use of a cursor and if you are using SQL Server 2K. Has a bit more info in it also. You can always trim the fields you do not need. ENJOY!


Select
TblOwner=left(u.name,12),
TblName=left(o.name,40),
i.MinLen,
MaxLen=i.xmaxlen,
TblSpace=8*i.DPages,
IndxSpace=8*(i.Used-i.DPages),
Reserved=8*i.Reserved,
Unused=8*(i.Reserved-i.used),
i.RowCnt
From sysindexes i
Join sysobjects o
On i.id=o.id
Join sysusers u
On o.uid=u.uid
where i.indid<2
And o.type='u'
And o.name<>'dtproperties'
Order by 1,2

Thanks

J. Kusch

 

[Sina]

Thank you both but I have to do it for sql server 6.5

How can I show the table names in one colume and the total rows in front of it nicely?

thanks much for your help.

 

[TitleistDBA]

This works in SQL 6.5. I just tested it. I also changed it so the table name is the column name.

SET NOCOUNT ON
DECLARE tables_cursor CURSOR
FOR
SELECT name FROM sysobjects WHERE type = 'U'
OPEN tables_cursor

DECLARE @tablename varchar(255)

FETCH NEXT FROM tables_cursor INTO @tablename

WHILE (@@fetch_status <> -1)
BEGIN
--select @tablename
EXEC ('Select count(*) as' +' '+ @tablename +' from '+ @tablename)

FETCH NEXT FROM tables_cursor INTO @tablename
END
DEALLOCATE tables_cursor
SET NOCOUNT OFF

http://www.acushnetcompany.com

 

[Sina]

I want to get the format nice and correct.

To be able to display such as


tablename Row count
tablename Row count
tablename Row count
tablename Row count
tablename Row count
tablename Row count
tablename Row count

You know what I mean.

right now the code show the result as such.

Tablename
-------------------
rowcount

Tablename
-------------------
rowcount
Tablename
-------------------
rowcount
Tablename
-------------------
rowcount
Tablename
-------------------
rowcount

thanks very much for your help.

 

[TitleistDBA]

Jays Example will give it to you in that format. but you have to remove the columns not in 6.5. Like this.

Select
TblName=left(o.name,40),
i.RowCnt
From sysindexes i
Join sysobjects o
On i.id=o.id
Join sysusers u
On o.uid=u.uid
where i.indid<2
And o.type='u'
And o.name<>'dtproperties'
Order by 1,2

http://www.acushnetcompany.com

 

[Sina]

Oh perfect.

Thanks a million