Looking for single system query to return the rowcount of all tables in a DB
-
I am currently working with MS SQL Server versions 7.0 and higher.
I have worked with Sybase in the past, and I had an extremely useful query which worked on the system tables in a Db to return a list of all the tables in the Db and their rowcount. See bottom of email.
It worked on the system tables so it was very fast and didn't have the performance hit of a manual rowcount on each table in the Db.
However it doesn't work in SQL Server as SQl Server doesn't have the "rowcnt" function.
Is their a SQL Server equivalent for this *extremely* useful query?
Tom.
Sybase Query
=========================
select o.crdate, o.name, rowcnt(i.doampg)
from sysobjects o, sysindexes i
where type = 'U' and o.id = i.id
and rowcnt(i.doampg)!= 0
order by rowcnt(i.doampg) desc, o.crdate, o.name
go
-
If you want ACTUAL counts than the answer is no. IF you want estimated counts you can pull that from Sysindexes BUT this can be VERY incorrect....
Good Hunting!AJ Ahrens
webmaster@kritter.net -
How inaccurate are we talking about?
Off by +/- 10 to 20% of actual table rowcount?
Does it depend on how recently your indexes have been optimised?
Can you send me a query to do this and I will check it out.
Thanks!
Tom.
-
This may be overkill for your needs, but you can always drop out the code you do not need.
SET NOCOUNT ON
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[DataStats]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[DataStats]
GO
CREATE TABLE [dbo].[DataStats](
[TableName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ColumnName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DataType] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MaxLength] [varchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CountAllRecords] [int] NULL,
[PercentFull] [decimal](18, 3) NULL,
[MinRecord] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[MaxRecord] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]
GO
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Counts]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Counts]
GO
CREATE TABLE [dbo].[Counts](
[TableCount] [int] NULL,
[ColumnCount] [int] NULL ) ON [PRIMARY]
GO
--TRUNCATE TABLE DataInformation
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
INTO #TableInformation
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'dbo'
AND TABLE_NAME NOT LIKE 'sys%'
AND TABLE_NAME NOT LIKE 'db%'
DECLARE @TableName varchar(100),
@ColumnName varchar(100),
@DataType varchar(50),
@MaxLength varchar(25),
@TableCount decimal(18,3),
@ColumnCount decimal(18,3),
@SLQUpdate varchar(1000)
DECLARE TableInformation INSENSITIVE CURSOR FOR
SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM #TableInformation
WHERE TABLE_NAME IS NOT NULL
AND COLUMN_NAME IS NOT NULL
ORDER BY TABLE_NAME, COLUMN_NAME ASC
OPEN TableInformation
FETCH NEXT FROM TableInformation INTO
@TableName, @ColumnName, @DataType, @MaxLength
IF @@FETCH_STATUS = 0
BEGIN
IF ISNULL( @TableName, 'N/A') <> 'N/A' AND ISNULL( @ColumnName, 'N/A') <> 'N/A'
BEGIN
SELECT @SLQUpdate = 'TRUNCATE TABLE [Counts] ' + CHAR(13) +
'INSERT INTO [Counts]( TableCount) ' + CHAR(13) +
'SELECT COUNT(*) FROM [' + @TableName + ']'
-- PRINT @SLQUpdate
EXECUTE( @SLQUpdate)
SELECT @TableCount = (SELECT TableCount FROM [Counts])
SELECT @SLQUpdate = 'UPDATE [Counts] SET ' + CHAR(13) + CHAR(13) +
' ColumnCount = ( SELECT COUNT( [' + @ColumnName + ']) ' + CHAR(13) + CHAR(13) +
' FROM [' + @TableName + ']' + CHAR(13) + CHAR(13) +
' WHERE [' + @ColumnName + '] IS NOT NULL) ' + CHAR(13) +
' WHERE TableCount = ' + CONVERT( varchar, @TableCount)
-- PRINT @SLQUpdate
EXECUTE( @SLQUpdate)
SELECT @ColumnCount = (SELECT ColumnCount FROM [Counts])
END
INSERT INTO DataStats( TableName, ColumnName, DataType, MaxLength, CountAllRecords, PercentFull)
SELECT ISNULL( @TableName, 'N/A'), ISNULL( @ColumnName, 'N/A'),
ISNULL( @DataType, 'N/A'), ISNULL( @MaxLength, 'N/A'),
@TableCount,
(CASE
WHEN @ColumnCount = 0
THEN 0
WHEN @ColumnCount IS NULL
THEN 0
WHEN @TableCount = 0
THEN 0
WHEN @TableCount IS NULL
THEN 0
WHEN ( ISNULL( @ColumnCount, 2) / ISNULL( @TableCount, 1)) = 2
THEN 0
ELSE @ColumnCount / @TableCount
END) * 100
FETCH NEXT FROM TableInformation INTO
@TableName, @ColumnName, @DataType, @MaxLength
END
CLOSE TableInformation
DEALLOCATE TableInformation
DROP TABLE #TableInformation
IF EXISTS( SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[Counts]')
AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE [dbo].[Counts]
GO
----------------------------------------------------------------------------------------
DECLARE @TableName2 varchar(100),
@ColumnName2 varchar(100),
@SLQUpdate2 varchar(1000)
DECLARE UpdateTableInformation INSENSITIVE CURSOR FOR
SELECT TableName, ColumnName
FROM DataStats
ORDER BY TableName, ColumnName ASC
OPEN UpdateTableInformation
FETCH NEXT FROM UpdateTableInformation INTO @TableName2, @ColumnName2
IF @@FETCH_STATUS = 0
BEGIN
IF ISNULL( @TableName2, 'N/A') <> 'N/A' AND ISNULL( @ColumnName2, 'N/A') <> 'N/A'
BEGIN
SELECT @SLQUpdate2 = ' UPDATE [DataStats] SET ' + CHAR(13) + CHAR(9) +
' MaxRecord = ( SELECT CONVERT( varchar, MAX( [' + @ColumnName2 + '])) ' + CHAR(13) + CHAR(9) + CHAR(9) + CHAR(9) +
' FROM [' + @TableName2 + ']' + CHAR(13) + CHAR(9) + CHAR(9) + CHAR(9) +
' WHERE [' + @ColumnName2 + '] IS NOT NULL), ' + CHAR(13) + CHAR(9) +
' MinRecord = ( SELECT CONVERT( varchar, MIN( [' + @ColumnName2 + '])) ' + CHAR(13) + CHAR(9) + CHAR(9) + CHAR(9) +
' FROM [' + @TableName2 + ']' + CHAR(13) + CHAR(9) + CHAR(9) + CHAR(9) +
' WHERE [' + @ColumnName2 + '] IS NOT NULL) ' + CHAR(13) +
' WHERE TableName = ' + CHAR(39) + CONVERT( varchar, @TableName2) + CHAR(39) + CHAR(13) +
' AND ColumnName = ' + CHAR(39) + CONVERT( varchar, @ColumnName2) + CHAR(39) + CHAR(13)
-- PRINT @SLQUpdate2
EXECUTE( @SLQUpdate2)
END
FETCH NEXT FROM UpdateTableInformation INTO @TableName2, @ColumnName2
END
CLOSE UpdateTableInformation
DEALLOCATE UpdateTableInformation
GO
I wasn't born stupid - I had to study.
-
Thanks.
Tom.
-
You actually already had everything you need
select o.crdate, o.name, i.rowcnt
from sysobjects o, sysindexes i
where type = 'U' and o.id = i.id
and i.rowcnt!= 0
order by i.rowcnt desc, o.crdate, o.name
go
rowcnt is a column in sysindexes.
While this is usually correct, it is not guaranteed to. You might want to run a DBCC UPDATEUSAGE before this query, to get more accurate results. The only really reliable way I know of, is to do a COUNT() to get exact numbers.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I wholeheartedly agree Frank. I posted my code because it does do a count, albiet that count is for each row in a table. We would download data from another source and this would verify the integrity. Like I said, it was probably overkill for this question, but it should be simple to tweak it.
I wasn't born stupid - I had to study.
-
The sysindex.rowcnt field is of type binary.
How do I convert that to a human readable value?
Tom.
-
Warning: The previous code will NOT work if you have more than one index on a table!!!!
the sysindexes table tells you how many rows in each index, not in each table. What you want is one number of records for each table.
select o.name, max(i.rows)
from sysobjects o
join sysindexes i on o.id = i.id
where o.type = 'U'
group by o.name
order by o.name
compute sum(max(i.rows))
Let me know if this works for you.
-
or you can try this.
sp_msforeachtable @command1 = 'print ''?''' ,
@command2 = 'select count (*) from ?'
sp_msforeachtable will perform up to three commands on each table in the database. When running, it replaces the ? with the name of the table.
-
someone's contribution to the site, almost identical to Frnaks suggestion, but i probably renamed it to be convenient to my naming convention:
create procedure sp__CountTableRows
as
begin
SELECT sysobjects.name, sysindexes.[rows]
FROM sysobjects
INNER JOIN
sysindexes
ON sysobjects.id = sysindexes.id
WHERE (sysobjects.type = 'u')
AND (sysindexes.indid = 1)
ORDER BY sysindexes.rows desc,sysobjects.name
end
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Sorry, Lowell. Your code won't always work.
Not every table has an index with an index id (indid) of 1. You'll miss those. If an index is created, and then deleted, you'll be missing that index #.
run this.
--this tells you how all the tables you have.
select name from sysobjects
where xtype = 'U'
order by name
-- this tells you which tables have, and which don't have, an indid of 1.
select o.name, i.*
from sysobjects o
left join sysindexes i on o.id = i.id
and i.indid = 1
where o.xtype = 'U'
order by o.name
The way out of this, is to ignore the index id, and just use the index that has the greatest number of rows.
select o.name, max(i.rows) as number_of_rows
from sysobjects o
join sysindexes i on o.id = i.id
where o.xtype = 'U'
group by o.name
order by o.name
-
Sara thanks for the input;
your suggestions showed me that in my real world sample database, your script found ten more tables, and their table counts, than mine did;
i just inserted the contents of either sample into temp tables and joined for the differences:
SELECT sysobjects.name, MAX(sysindexes.rows) AS NUMROWS
into #tb1
FROM sysobjects
INNER JOIN sysindexes ON sysobjects.id = sysindexes.id
WHERE sysobjects.xtype = 'U'
GROUP by sysobjects.name
ORDER BY NUMROWS DESC,sysobjects.name
SELECT sysobjects.name, sysindexes.[rows]
into #tb2
FROM sysobjects
INNER JOIN
sysindexes
ON sysobjects.id = sysindexes.id
WHERE (sysobjects.type = 'U')
AND (sysindexes.indid = 1)
ORDER BY sysindexes.rows desc,sysobjects.name
select * from #tb1 full outer join #tb2 on #tb1.name=#tb2.name
i've tweaked my stored proc that i use to incorporate your suggestion.
Thanks!
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply