SQL Server :Table counts
' SELECT COUNT(*) ' on those big tables can be a little invasive. Not only fairly timely, but the used resources can be extreme. Here are a few different methods for using the COUNT function. Understand, these methods are just selective counts, per table. If you need to retrieve counts across multiple tables, possibly for comparison, you could try something like this: Counts Across Multiple Tables
Or even this, to compare counts across databases: Compare Two Databases
-- Just one table:
1. Return count of all records in the table
SELECT COUNT(*) FROM dbo.TABLE
2. Return count of all values for the given COLUMN, excluding NULL values:
SELECT COUNT(COLUMN) FROM dbo.TABLE
3. Return the count of distinct values for the given COLUMN:
SELECT COUNT(DISTINCT(COLUMN)) FROM dbo.TABLE
(Does not work with MSAccess.)
-- All tables:
1. Return a count for all tables.
CREATE TABLE dbo.TABLECOUNTS
(
TABLENAME VARCHAR(255) NOT NULL,
RECORDCOUNT INT NOT NULL
)
GO
EXEC sp_msforeachtable
"INSERT INTO TABLECOUNTS SELECT '?' AS TABLENAME, COUNT(*) FROM ?"
SELECT * FROM dbo.TABLECOUNTS
-- Of course, you'll see the fully qualified table names, like this: [dbo].[tablename]. Use the following updates, if desired, to get rid of the '[dbo].', and the ending right bracket, for each TABLENAME:
UPDATE dbo.TABLECOUNTS
SET TABLENAME = REPLACE(TABLENAME,'[dbo].[','')
UPDATE dbo.TABLECOUNTS
SET TABLENAME = REPLACE(TABLENAME,']','')
SELECT * FROM TABLECOUNTS
DROP TABLE TABLECOUNTS
2. Notably faster, but remember -- it is only a close estimate.
The sysindexes table is not updated real time, so your numbers may be a little less than accurate.
SELECT SO.Name, SI.rows
FROM sysindexes SI, SysObjects SO
WHERE SI.id = SO.ID
AND SI.indid < 2
AND SO.Type = 'U'
ORDER BY so.name
3. Similar to #2, but statistics are updated first with DBCC UPDATEUSAGE.
DECLARE @SQL NVARCHAR(255)
SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
EXEC sp_executeSQL @SQL
SELECT
Schema_name(t.schema_id) AS SchemaName,
t.name AS TableName,
i.rows AS [Rows]
FROM
sys.tables AS t INNER JOIN sys.sysindexes AS i
ON t.object_id = i.id
AND i.indid < 2
ORDER BY
t.name
Or even this, to compare counts across databases: Compare Two Databases
-- Just one table:
1. Return count of all records in the table
SELECT COUNT(*) FROM dbo.TABLE
2. Return count of all values for the given COLUMN, excluding NULL values:
SELECT COUNT(COLUMN) FROM dbo.TABLE
3. Return the count of distinct values for the given COLUMN:
SELECT COUNT(DISTINCT(COLUMN)) FROM dbo.TABLE
(Does not work with MSAccess.)
-- All tables:
1. Return a count for all tables.
CREATE TABLE dbo.TABLECOUNTS
(
TABLENAME VARCHAR(255) NOT NULL,
RECORDCOUNT INT NOT NULL
)
GO
EXEC sp_msforeachtable
"INSERT INTO TABLECOUNTS SELECT '?' AS TABLENAME, COUNT(*) FROM ?"
SELECT * FROM dbo.TABLECOUNTS
-- Of course, you'll see the fully qualified table names, like this: [dbo].[tablename]. Use the following updates, if desired, to get rid of the '[dbo].', and the ending right bracket, for each TABLENAME:
UPDATE dbo.TABLECOUNTS
SET TABLENAME = REPLACE(TABLENAME,'[dbo].[','')
UPDATE dbo.TABLECOUNTS
SET TABLENAME = REPLACE(TABLENAME,']','')
SELECT * FROM TABLECOUNTS
DROP TABLE TABLECOUNTS
2. Notably faster, but remember -- it is only a close estimate.
The sysindexes table is not updated real time, so your numbers may be a little less than accurate.
SELECT SO.Name, SI.rows
FROM sysindexes SI, SysObjects SO
WHERE SI.id = SO.ID
AND SI.indid < 2
AND SO.Type = 'U'
ORDER BY so.name
3. Similar to #2, but statistics are updated first with DBCC UPDATEUSAGE.
DECLARE @SQL NVARCHAR(255)
SET @SQL = 'DBCC UPDATEUSAGE (' + DB_NAME() + ')'
EXEC sp_executeSQL @SQL
SELECT
Schema_name(t.schema_id) AS SchemaName,
t.name AS TableName,
i.rows AS [Rows]
FROM
sys.tables AS t INNER JOIN sys.sysindexes AS i
ON t.object_id = i.id
AND i.indid < 2
ORDER BY
t.name
If you've got v2005 or v2008, you should try to steer clear from querying sysindexes. It will work, but it is said to be removed in a future version. Probably best to use the DMV's instead, like this:
SELECT
o.name,
ddps.row_count
FROM
indexes i INNER JOIN sys.objects o
ON sys. i.OBJECT_ID = o.OBJECT_ID
INNER JOIN sys.dm_db_partition_stats ddps
ON i.OBJECT_ID = ddps.OBJECT_ID
AND i.index_id = ddps.index_id
WHERE
i.index_id < 2
AND o.is_ms_shipped = 0
ORDER BY
o.NAME