Here is one method:
====
DECLARE tablesList CURSOR FOR
-- Cursor to select all the tables from a database
SELECT table_name FROM information_schema.tables
WHERE table_type = 'BASE TABLE'
ORDER BY table_name

-- Scratch variables
DECLARE @tableCount INT
DECLARE @lcTableName VARCHAR(100)
DECLARE @sqlString NVARCHAR(1000)

-- Table that stores the output
CREATE TABLE #tableCounts (tableName VARCHAR(100), recordCount INT)

OPEN tablesList
FETCH NEXT FROM tablesList INTO @lcTableName
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- Form the string to determine the count. Execute it and store the list
in the temporary
-- table
SET @sqlString = 'SELECT @rowCount = COUNT(*) FROM ' + @lcTableName
EXEC sp_executesql @sqlString, N'@rowCount INT OUTPUT', @tableCount
OUTPUT
INSERT INTO #tableCounts VALUES (@lcTableName, @tableCount)

-- Fetch the next table in the list
FETCH NEXT FROM tablesList INTO @lcTableName
END

-- Cleanup the cursor
CLOSE tablesList
DEALLOCATE tablesList

-- Display data from the table and cleanup
SELECT * FROM #tableCounts
DROP TABLE #tableCounts
=====

Another option is to use SQLDMO from a COM compliant application (even SQL
Server). SQLDMO has certain methods that fetch this value directly.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp

[quoted text, click to view]