MS SQL 2005 (T-SQL) Row Count for Each Table

7 Responses

Comments RSS Feed.

1. Hans Callesen said

   on September 18, 2008

   replaced cursor with a table vriable & a while loop. More efficient , though not noticable with small datasets such as these:
   ——-
   – Select Database
   – This should be the only configuration you need
   USE Testing
   GO
   declare @Tables table (id integer identity(1,1),TName varchar(max))
   insert into @Tables (TName) SELECT Name FROM Sysobjects WHERE Type=’U’
   – Declare the @SQL Variable which will hold our dynamic sql
   DECLARE @SQL NVARCHAR(MAX);
   Declare @counter integer
   – Declare the @TblName Variable which will hold the name of the current table
   DECLARE @TblName NVARCHAR(MAX);
   SET @SQL = ”;
   select @counter = 1
   While @counter <= (select max(id) from @Tables)
   Begin–While Loop
   select @TblName = (select TName from @Tables where id = @Counter)
   – Appeand this table’s select count statement to our sql variable
   SET @SQL = @SQL + ‘ ( SELECT ”’+@TblName+”’ AS Table_Name,COUNT(*) AS Count FROM ‘+@TblName+’ ) UNION’;
   select @counter = @counter+1
   End–While Loop

   – Since we were adding the UNION at the end of each part, the last query will have
   – an extra UNION. Lets trim it off.
   SET @SQL = LEFT(@SQL,LEN(@SQL)-6);

   – Lets do an Order By. You can pick between Count and Table Name by picking which
   – line to execute below.
   SET @SQL = @SQL + ‘ ORDER BY Count’;
   –SET @SQL = @SQL + ‘ ORDER BY Table_Name’;

   – Now that our Dynamic SQL statement is ready, lets execute it.
   EXEC (@SQL);
   GO
   ——-

2. silver said

   on November 5, 2008

   try this
   use yourdatabasename

   SELECT name, rows FROM sysindexes where impid < 0 order by name

3. Dos said

   on November 6, 2008

   The sysindexes query is fine but relies upon your database stats being up-to-date.

   Also I found that square brackets were needed around the tablename on line 26 – in the dynamic query (some of my tables begin with a numeric character)

   instead of:
   SET @SQL = @SQL + ‘ ( SELECT ”’+@TblName+”’ AS Table_Name,COUNT(*) AS Count FROM ‘+@TblName+’ ) UNION’

   use:
   SET @SQL = @SQL + ‘ ( SELECT ”’+@TblName+”’ AS Table_Name,COUNT(*) AS Count FROM ['+@TblName+'] ) UNION’

4. Neal said

   on December 11, 2008

   Wouldn’t this be easier?!

   SELECT ‘select ”’+ name + ”’, count(*) from ‘ + name + ‘ union’
   FROM Sysobjects
   WHERE Type=’U’
   ORDER BY 1;

   Copy + paste the results, remove the trailing ‘union’ and add your ‘order by’ clause.

5. Colin said

   on January 15, 2009

   Great post. Only issue I had was that my tables all use different schemas and not the default “dbo” schema, so I had to join to the sys.schemas table on schema_id and then concatenate the two columns from the sys.objects and sys.schemas tables together to get it to work.

   Quick question: Is there a way to list the row counts as well as the table sizes?

6. Peter said

   on February 6, 2009

   Nice little post for this. I tweaked it for SQl Server 2000 and its limitation on varchar size. Left the cursor use in:

   – Create a cursor to loop through the System Ojects and get each table name
   DECLARE TBL_CURSOR CURSOR
   – Declare the SQL Statement to cursor through
   FOR (SELECT Name FROM Sysobjects WHERE Type=’U')

   – Declare the @SQL Variable which will hold our dynamic sql
   DECLARE @SQL VARCHAR(8000), @SQL2 VARCHAR(8000), @SQL3 VARCHAR(8000)
   SET @SQL = ”;
   SET @SQL2 = ”;
   SET @SQL3 = ”;
   – Declare the @TblName Variable which will hold the name of the current table
   DECLARE @TblName VARCHAR(255);

   – Open the Cursor
   OPEN TBL_CURSOR

   – Setup the Fetch While that will loop through our cursor and set @TblName
   FETCH NEXT FROM TBL_CURSOR INTO @TblName
   – Do this while we are not at the end of the record set
   WHILE (@@FETCH_STATUS -1)
   BEGIN
   – Appeand this table’s select count statement to our sql variable
   IF ASCII (@TblName) % 3 = 0
   SET @SQL = @SQL + ‘ ( SELECT ”’+@TblName+”’ AS Table_Name, COUNT(*) AS Count FROM ‘+@TblName+’ ) UNION’;

   IF ASCII (@TblName) % 3 = 1
   SET @SQL2 = @SQL2 + ‘ ( SELECT ”’+@TblName+”’ AS Table_Name, COUNT(*) AS Count FROM ‘+@TblName+’ ) UNION’;

   IF ASCII (@TblName) % 3 = 2
   SET @SQL3 = @SQL3 + ‘ ( SELECT ”’+@TblName+”’ AS Table_Name, COUNT(*) AS Count FROM ‘+@TblName+’ ) UNION’;

   – Pull the next record
   FETCH NEXT FROM TBL_CURSOR INTO @TblName
   – End the Cursor Loop
   END

   – Close and Clean Up the Cursor
   CLOSE TBL_CURSOR
   DEALLOCATE TBL_CURSOR

   – Since we were adding the UNION at the end of each part, the last query will have
   – an extra UNION. Lets trim it off.
   –SET @SQL = LEFT(@SQL, LEN(@SQL)-6);
   –SET @SQL2 = LEFT(@SQL2, LEN(@SQL2)-6);
   SET @SQL3 = LEFT(@SQL3, LEN(@SQL3)-6);

   – Lets do an Order By. You can pick between Count and Table Name by picking which
   – line to execute below.
   –SET @SQL = @SQL + ‘ ORDER BY Count’;
   –SET @SQL = @SQL + ‘ ORDER BY Table_Name’;

   – Now that our Dynamic SQL statement is ready, lets execute it.
   EXEC (@SQL + @SQL2 + @SQL3 + ‘ ORDER BY Count’);
   –EXEC (@SQL + @SQL2 + @SQL3 + ‘ ORDER BY Table_Name’);
   GO

7. nausea said

   on June 2, 2009

   I used this:

   SELECT object_name(id) ,rowcnt
   FROM sysindexes
   WHERE indid IN (1,0) AND OBJECTPROPERTY(id, ‘IsUserTable’) = 1
   order by rowcnt desc