Cursor count rows problem

[odeddror]

Hi there,

I would like to count all database tables rows
And I wrote

Create table tblRowsCount
(
Database_name varchar(255),
Table_name varchar(255),
Row_Count int
)
GO
DECLARE @db_name VARCHAR(50)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
insert tblRowsCount
select Db_Name() AS Database_Name, object_name(id)as Table_Name,rowcnt as
Row_Count
from sysindexes
where indid in(1,0)
and objectproperty(id,'IsUserTable') = 1
Order By rowcnt desc
FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor

But from some reason it not move over to the next datebase
The cursor just doing loop where is the current database

How do I make it move to the next database?

Thanks
Oded Dror

[Uri Dimant]

Oded
Change Db_Name() to @db_name

"odeddror" <oded...@cox.net> wrote in message
news:1A93C01C-B8EA-41C8...@microsoft.com...

[Tibor Karaszi]

Below should get you started:

--Create table tblRowsCount
--(
--Database_name varchar(255),
--Table_name varchar(255),
--Row_Count int
--)
--GO
TRUNCATE TABLE tblRowsCount

GO
DECLARE @db_name VARCHAR(50)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')

DECLARE @sql varchar(1000)

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN

SET @sql =
'
INSERT INTO tblRowsCount(Database_name, Table_name, Row_Count)
SELECT ''' + @db_name + ''' AS Database_Name, t.name, i.rowcnt
FROM ' + QUOTENAME(@db_name) + '..sysindexes AS i inner join ' +
QUOTENAME(@db_name) + '.sys.tables AS t ON i.id = t.object_id
WHERE i.indid IN (0,1)
'
--PRINT @sql
EXEC(@sql)

FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor

GO

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"odeddror" <oded...@cox.net> wrote in message
news:1A93C01C-B8EA-41C8...@microsoft.com...

[odeddror]

Thank you very much
Both solutions works

"odeddror" <oded...@cox.net> wrote in message
news:1A93C01C-B8EA-41C8...@microsoft.com...