Retrieving the total number of records in a SQL instance

Question

Post reply

Retrieving the total number of records in a SQL instance

tarr94

SSCommitted

Points: 1684

September 4, 2020 at 3:36 pm

#3786441

Hello,

Is anyone aware of a good approach for retrieving the total number of records across all tables and all databases within a SQL instance? I've attempted to write a query for it, but it isn't working as expected.

SET NOCOUNT ON 


DECLARE @Counter INT, @MaxId INT, @DbName VARCHAR(100), @Sql VARCHAR(100)

SELECT @Counter = min(database_id) , @MaxId = max(database_id)
FROM sys.databases

WHILE(@Counter IS NOT NULL
      AND @Counter <= @MaxId)

BEGIN

PRINT @DbName

SELECT @DbName = (SELECT name FROM sys.databases WHERE database_id = @Counter)

SELECT @Sql = 'USE ' + @DbName

print @Sql

EXEC sp_sqlexec @Sql

        CREATE TABLE #TableCounts
        ( 
            TableName VARCHAR(500), 
            CountOf INT 
        ) 

        INSERT #TableCounts
            EXEC sp_msForEachTable 
                'SELECT PARSENAME(''?'', 1), 
                COUNT(*) FROM ? WITH (NOLOCK)' 


SELECT DbName = @DbName, Total = SUM(CountOf)
        FROM #TableCounts

DROP TABLE #TableCounts

SET @Counter  = @Counter  + 1 

END

The result is that each database comes back with "0" as their total.

I suspect the issue has something to do with the database not getting set properly, but I would greatly appreciate any assistance with this.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

Do you want an accurate count, or are you happy with an approximation?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

tarr94 SSCommitted Points: 1684 More actions · Answer 2

Hi Thom,

I guess it would depend on how close of an approximation it is. I think 95% accurate approximation might be acceptable.

This reply was modified 3 years, 7 months ago by tarr94.

Thom A SSC Guru Points: 99126 More actions · Answer 3

If you're happy with approximations, then you can use one of the techniques outlined in the article How to count the number of rows in a table in SQL Server by Jes Schultz (link to Brent Ozar). As you want this for each database as well, then I'm going to (shamelessly) use one of my own stored procedures, sp_foreachdatabase to get this to run against each database: A CURSOR free version of sp_msforeachdb.

For a single database, to get the values by table would be as follows (I'm going to use the DMV solution):

SELECT OBJECT_NAME(ps.object_id), SUM(ps.row_count) AS rows
FROM sys.dm_db_partition_stats ps
     JOIN sys.tables t ON ps.object_id = t.object_id
WHERE ps.index_id < 2
  AND t.type = 'U'
GROUP BY OBJECT_NAME(ps.object_id);

I need to add to JOIN to sys.tables as we want all the tables, but not the system ones.To therefore do this for all the databases (I assume excluding the system ones), we can therefore do something like this, using my previously linked Stored Procedure:

USE master;
GO

DECLARE @Pre_Command nvarchar(MAX) = N'CREATE TABLE #RowCount(DatabaseName sysname, TableName sysname, Rows bigint);';
DECLARE @Command nvarchar(MAX) = N'INSERT INTO #RowCount(DatabaseName, TableName, Rows)
SELECT &,
       OBJECT_NAME(ps.object_id),
       SUM(ps.row_count) AS rows
FROM sys.dm_db_partition_stats ps
     JOIN sys.tables t ON ps.object_id = t.object_id
WHERE ps.index_id < 2
  AND t.type = ''U''
GROUP BY OBJECT_NAME(ps.object_id);';
DECLARE @Post_Command nvarchar(MAX) = N'SELECT * FROM #RowCount;';
DECLARE @Command_Run nvarchar(MAX);

EXEC sp_foreachdatabase @Pre_Command = @Pre_Command,
                        @Command = @Command,
                        @Post_Command = @Post_Command,
                        @Skip_system = 1,
                        @Auto_Use = 1,
                        @Command_Run = @Command_Run OUTPUT;

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Jeff Moden SSC Guru Points: 1003864 More actions · Answer 4

Ok... I have a question, especially seems to be coming up a lot lately.

To what end does someone need this type of information? What will the information ultimately be used for?

Especially because of varying width rows even in the same table, using only row counts have almost no value for anything especially since the number of rows are frequently out of date just as soon as you figure out how many rows there are.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003864 More actions · Answer 5

Jeff Moden wrote:

Ok... I have a question, especially seems to be coming up a lot lately.
To what end does someone need this type of information? What will the information ultimately be used for?
Especially because of varying width rows even in the same table, using only row counts have almost no value for anything especially since the number of rows are frequently out of date just as soon as you figure out how many rows there are.

Guess the OP flew the coup on this one. Given the reasons above, I just can't imagine there being any value to knowing only how many rows are instance. It just doesn't make sense. If someone at work told me to do this, I'd really need to know why other than some reason like "because we need it" or "because I told you so". This appears to be a complete waste of talent, time, and money. The only reason I can see someone even asking this question would be as an interview question.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

tarr94 SSCommitted Points: 1684 More actions · Answer 6

Hi Thom,

Thank you for the suggestion, I'll have to give that a shot!

Hi Jeff,

I was asked to pull this information because our cyber insurance company wants an approximation of the total number of rows across all SQL instances. I imagine this has implications for the insurance plan we end up using, but I'm not the one interacting with them, so I can't say for sure.

Jeff Moden SSC Guru Points: 1003864 More actions · Answer 7

tarr94 wrote:

Hi Jeff,
I was asked to pull this information because our cyber insurance company wants an approximation of the total number of rows across all SQL instances. I imagine this has implications for the insurance plan we end up using, but I'm not the one interacting with them, so I can't say for sure.

Interesting. You'd think they'd be more interested in the total number of bytes. Thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99126 More actions · Answer 8

Jeff Moden wrote:

tarr94 wrote:
Hi Jeff,
I was asked to pull this information because our cyber insurance company wants an approximation of the total number of rows across all SQL instances. I imagine this has implications for the insurance plan we end up using, but I'm not the one interacting with them, so I can't say for sure.
Interesting. You'd think they'd be more interested in the total number of bytes. Thank you for the feedback.

Agreed. Some tables can be very small and contain re-creatable data; such as a calendar table. Others contain a significant amount of data that can't afford to be lost; such as transaction data. The former has next to no insurance interest, where as the latter far more. A very odd perspective to get the rows, in my opinion (I work in the Insurance sector for context).

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 9

If they want rows for insurance, getting the rough size from the metadata tables and then the table size might give you a guess of rows.