Get record count for a all databases

,

Use in query analyzer or mgmt studio

/* Created by Brant van Lindenburg 7/31/2008 */
DECLARE @name VARCHAR(50) -- database name 
DECLARE @i int
declare @vsql varchar(1900)

DECLARE db_cursor CURSOR FOR 
SELECT name FROM master.dbo.sysdatabases 
--you can comment out this next line if you want thes included
WHERE NOT name IN ('tempdb','master','model','msdb')
order by name

OPEN db_cursor 
FETCH NEXT FROM db_cursor INTO @name 

WHILE @@FETCH_STATUS = 0 
BEGIN 
-- SET @fileName = @path + @name + '_' + @fileDate + '.BAK' 
-- BACKUP DATABASE @name TO DISK = @fileName 
 Select @name
--The select statement is courtesy of James Rea found on SQLServerCentral.com
 set @vSQL = 'Use [' + @name +'] ' +
         '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)
        ORDER BY i.rowcnt DESC 
        COMPUTE SUM(i.rowcnt)'
 Print @vsql
 exec(@vsql)

 FETCH NEXT FROM db_cursor INTO @name 
END 

CLOSE db_cursor 
DEALLOCATE db_cursor

Rate

4.67 (3)

Share

Share

Rate

4.67 (3)