Prev: How to find all tables and fields using max lengths in the database?
Next: Want to modify this procedure to have a range of periods rather than a single period
From: odeddror on 10 Jul 2010 11:02
Hi there,

I would like to get the rows count for all databases
but from some reasion it bring me the rows of wich database I'm in
How can I show the acual tables and rows count for each database?

USE Northwind
GO
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 sys.databases
WHERE database_id NOT IN (1,2,3,4)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT #tblRowsCount
SELECT @DB_NAME As Database_Name,
a.name AS Table_Name,
b.row_count AS Row_Count
FROM sys.objects AS a
INNER JOIN sys.dm_db_partition_stats AS b ON a.object_id = b.object_id
WHERE (a.type = 'u') AND (b.index_id < 2)
AND b.row_count > 0
ORDER BY b.row_count desc
FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
GO
SELECT * from #tblRowsCount order by Database_Name
GO
DROP TABLE #tblRowsCount
GO

Thnaks,
Oded Dror

From: Tom Cooper on 10 Jul 2010 13:02
You need to specify the database in the query for each iteration through the
cursor. That means you will need to use dynamic SQL. Also, you want almost
every cursor to be declared LOCAL, and cursors used by reading sequentially
through every row should be declared FAST_FORWARD, so something like

CREATE TABLE #tblRowsCount
(
Database_name varchar(255),
Table_name varchar(255),
Row_Count int
)
GO
DECLARE @db_name VARCHAR(255)
DECLARE @SQLCommand VARCHAR(MAX)
DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT name from sys.databases
WHERE database_id NOT IN (1,2,3,4)
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLCommand = 'USE [' + @DB_NAME + ']
INSERT #tblRowsCount
SELECT ''' + @DB_NAME + ''' As Database_Name,
a.name AS Table_Name,
b.row_count AS Row_Count
FROM sys.objects AS a
INNER JOIN sys.dm_db_partition_stats AS b ON a.object_id = b.object_id
WHERE (a.type = ''u'') AND (b.index_id < 2)
AND b.row_count > 0'EXEC (@SQLCommand)
FETCH NEXT FROM db_cursor INTO @db_name
END
CLOSE db_cursor
DEALLOCATE db_cursor
GO
SELECT * from #tblRowsCount order by Database_Name, Row_Count Desc
GO
DROP TABLE #tblRowsCount
GO

Tom

"odeddror" <odeddror(a)cox.net> wrote in message
news:2861E106-E247-4936-B7DB-FB3C2A296D2E(a)microsoft.com...
> Hi there,
>
> I would like to get the rows count for all databases
> but from some reasion it bring me the rows of wich database I'm in
> How can I show the acual tables and rows count for each database?
>
> USE Northwind
> GO
> 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 sys.databases
> WHERE database_id NOT IN (1,2,3,4) OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @db_name
> WHILE @@FETCH_STATUS = 0
> BEGIN
> INSERT #tblRowsCount SELECT @DB_NAME As Database_Name, a.name AS
> Table_Name, b.row_count AS Row_Count
> FROM sys.objects AS a INNER JOIN sys.dm_db_partition_stats AS b ON
> a.object_id = b.object_id
> WHERE (a.type = 'u') AND (b.index_id < 2) AND b.row_count > 0
> ORDER BY b.row_count desc
> FETCH NEXT FROM db_cursor INTO @db_name
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
> GO
> SELECT * from #tblRowsCount order by Database_Name
> GO
> DROP TABLE #tblRowsCount
> GO
>
> Thnaks,
> Oded Dror
>

From: odeddror on 10 Jul 2010 14:20
Tom,

Thank you very much it works

Oded

"Tom Cooper" <tomcooper(a)comcast.net> wrote in message
news:ODwjyGFILHA.2276(a)TK2MSFTNGP06.phx.gbl...
> You need to specify the database in the query for each iteration through
> the cursor. That means you will need to use dynamic SQL. Also, you want
> almost every cursor to be declared LOCAL, and cursors used by reading
> sequentially through every row should be declared FAST_FORWARD, so
> something like
>
> CREATE TABLE #tblRowsCount
> (
> Database_name varchar(255),
> Table_name varchar(255),
> Row_Count int
> )
> GO
> DECLARE @db_name VARCHAR(255)
> DECLARE @SQLCommand VARCHAR(MAX)
> DECLARE db_cursor CURSOR LOCAL FAST_FORWARD FOR
> SELECT name from sys.databases
> WHERE database_id NOT IN (1,2,3,4)
> OPEN db_cursor
> FETCH NEXT FROM db_cursor INTO @db_name
> WHILE @@FETCH_STATUS = 0
> BEGIN
> SET @SQLCommand = 'USE [' + @DB_NAME + ']
> INSERT #tblRowsCount
> SELECT ''' + @DB_NAME + ''' As Database_Name,
> a.name AS Table_Name,
> b.row_count AS Row_Count
> FROM sys.objects AS a
> INNER JOIN sys.dm_db_partition_stats AS b ON a.object_id = b.object_id
> WHERE (a.type = ''u'') AND (b.index_id < 2)
> AND b.row_count > 0'EXEC (@SQLCommand)
> FETCH NEXT FROM db_cursor INTO @db_name
> END
> CLOSE db_cursor
> DEALLOCATE db_cursor
> GO
> SELECT * from #tblRowsCount order by Database_Name, Row_Count Desc
> GO
> DROP TABLE #tblRowsCount
> GO
>
> Tom
>
> "odeddror" <odeddror(a)cox.net> wrote in message
> news:2861E106-E247-4936-B7DB-FB3C2A296D2E(a)microsoft.com...
>> Hi there,
>>
>> I would like to get the rows count for all databases
>> but from some reasion it bring me the rows of wich database I'm in
>> How can I show the acual tables and rows count for each database?
>>
>> USE Northwind
>> GO
>> 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 sys.databases
>> WHERE database_id NOT IN (1,2,3,4) OPEN db_cursor
>> FETCH NEXT FROM db_cursor INTO @db_name
>> WHILE @@FETCH_STATUS = 0
>> BEGIN
>> INSERT #tblRowsCount SELECT @DB_NAME As Database_Name, a.name AS
>> Table_Name, b.row_count AS Row_Count
>> FROM sys.objects AS a INNER JOIN sys.dm_db_partition_stats AS b
>> ON a.object_id = b.object_id
>> WHERE (a.type = 'u') AND (b.index_id < 2) AND b.row_count > 0
>> ORDER BY b.row_count desc
>> FETCH NEXT FROM db_cursor INTO @db_name
>> END
>> CLOSE db_cursor
>> DEALLOCATE db_cursor
>> GO
>> SELECT * from #tblRowsCount order by Database_Name
>> GO
>> DROP TABLE #tblRowsCount
>> GO
>>
>> Thnaks,
>> Oded Dror
>>
>

 | 
Pages: 1
Prev: How to find all tables and fields using max lengths in the database?
Next: Want to modify this procedure to have a range of periods rather than a single period