counting tables and rows

if you have a lot of tables in a database, you might want to count them by some attribute, such as schema_name or table_name. Use this

USE database
SELECT * from information_schema.tables
WHERE table_type = ‘base table’
AND TABLE_NAME LIKE ‘ib%’
and table_schema = ‘HIE_PM’

The table_schema might only be dbo, but maybe not.

To compare each table and the number of rows, try

SELECT
sysobjects.Name
, sysindexes.Rows
FROM
sysobjects
INNER JOIN sysindexes
ON sysobjects.id = sysindexes.id
WHERE
type = ‘U’
AND sysindexes.IndId < 2
ORDER BY sysindexes.Rows desc

Change it to this to get the total sum of rows in the database, which would be across the tables

SELECT sum (sysindexes.Rows)
FROM
sysobjects
INNER JOIN sysindexes
ON sysobjects.id = sysindexes.id
WHERE
type = 'U'
AND sysindexes.IndId < 2

Advertisements