Blogger open from Work? Oh, and rowcounts...

Wonder how long this is going to last... :-)

It used to be that a blog entry was part of my morning routine for a while. Not lately, and not for a while since my access to blogger from work has been somewhat... restricted. Nowhere more so than from my current client.

However, it seems that I have that access for the moment, and I should at least try to do something constructive with it. So with that in mind, here's a handy script to get the rowcounts for all your tables. This is fast enough to get the rowcounts for all tables quicker than you could do it for a single table using COUNT(*). Enjoy.

-D.

/* Select rowcounts for all tables in a single DB.
   MUCH faster than SELECT COUNT(*)
*/

-- 2005 and up.
SELECT 
  s.name AS [Schema],
  t.name AS [Table], 
  p.rows AS [RowCount]
FROM sys.partitions p
INNER JOIN sys.tables t
  ON p.object_id = t.object_id
INNER JOIN sys.schemas s
  ON t.schema_id = s.schema_id
WHERE index_id < 2
ORDER BY s.name, t.name

-- 2000 and earlier.
SELECT 
  u.name AS [Schema],
  o.name AS [Table],
  i.rowcnt AS [RowCount]
FROM dbo.sysobjects o
INNER JOIN dbo.sysindexes i
  ON o.id = i.id
INNER JOIN dbo.sysusers u
  ON o.uid = u.uid
WHERE i.indid < 2
  AND o.xtype = 'U'
ORDER BY u.name, o.name