• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 806
  • Last Modified:

SQL Statistics

I want to retrieve the statistics on a table and store them in a different table for historical purposes.
I need things like Row count etc but I do not want to do a count(*) if these values are stored somewhere.
I know one can use DBCC SHOW_STATISTICS but does anyone have any code that simplifies this - in other words loops through all the user tables and get the statistics for them?
or does anyone know where these values are stored.

Thanks
Kathy
0
azrakdragon
Asked:
azrakdragon
  • 3
  • 2
1 Solution
 
_ys_Commented:
A basic query would be:

SELECT      name, rows
FROM      sysIndexes
WHERE      indid = 0

This returns the table-name and rowcount. Keep in mind that the row count is not guaranteed to be updated real time.
0
 
azrakdragonAuthor Commented:
Hi ys

Would this only apply to tables that have indexes on them??
0
 
_ys_Commented:
I can't imagine a table that doesn't have at least one. I mean when you define a PRIMARY KEY an index (usually clustered) is created on your behalf. Unless your tables don't have a PRIMARY KEY defined, and I would advise against it.
0
 
azrakdragonAuthor Commented:
I understand what you are saying, perhaps I should have mentioned I want this information for a data warehouse. Unique columns are defined by Unique indexes but I cannot guarantee that every single table has an index especially our aggregated tables.

What I am ultimately looking for is the information displayed on the "Taskpad" view under Table Info on a database. Surely this kind of information is calculated and stored somewhere or is it calculated on the fly??
Where or how can I get this kind of information.

Regards
Kathy
0
 
_ys_Commented:
In that case COUNT(*) is what you gotta do.

If you're using MS SQL, here's a fully functional script:

---------------x---------------
CREATE TABLE dbo.table_count
(
       table_name NVARCHAR(128) NOT NULL PRIMARY KEY
      ,row_count INTEGER NOT NULL
)

INSERT      dbo.table_count
EXEC      sp_msForEachTable 'SELECT ''?'', COUNT(*) FROM ?'

SELECT      *
FROM      dbo.table_count

DROP TABLE dbo.table_count
---------------x---------------
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now