SQL Server Row Count for all Tables in a Database

Getting the row count from each table one by one and comparing and consolidating the results can be a tedious task. Hence any script/solution which can get the row count information from all the tables in a database can be really helpful and effective thereby considerably reducing the effort involved. In this tip we will see four different approaches to get the row counts from all the tables in a SQL Server database.

Let's take a look at each of the approaches:

• sys.partitions Catalog View
• sys.dm_db_partition_stats Dynamic Management View (DMV)
• sp_MSforeachtable System Stored Procedure
•  COALESCE() Function

Approach 1: sys.partitions Catalog View

sys.partitions is an Object Catalog View and contains one row for each partition of each of the tables and most types of indexes (Except Fulltext, Spatial, and XML indexes). Every table in SQL Server contains at least one partition (default partition) even if the table is not explicitly partitioned.

The T-SQL query below uses the sys.partitions Catalog View to capture the row counts for all tables in a database.

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO

Below are the highlights of this approach:

1.  Requires membership in the public role.
2. Can be used even when working with source systems which offer limited privileges such as read-only. 

Approach 2: sys.dm_db_partition_stats Dynamic Management View (DMV)

sys.dm_db_partition_stats is a Dynamic Management View (DMV) which contains one row per partition and displays the information about the space used to store and manage different data allocation unit types - IN_ROW_DATA, LOB_DATA and ROW_OVERFLOW_DATA.

The T-SQL query below uses the sys.dm_db_partition_stats DMV to capture the row counts for all tables in a database.

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sdmvPTNS.row_count) AS [RowCount]
FROM
      sys.objects AS sOBJ
      INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
            ON sOBJ.object_id = sdmvPTNS.object_id
WHERE 
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND sdmvPTNS.index_id < 2
GROUP BY
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
GO

Below are the highlights of this approach:

1. VIEW DATABASE STATE permissions are required in the database.
2. The values in the sys.dm_db_partition_stats DMV are reset on server restart or when an object/partition is dropped and recreated.

In general, querying the Dynamic Management Views (DMVs), requires VIEW SERVER STATE or VIEW DATABASE STATE permissions based on the Dynamic Management View/Function which is being queried.

---

Approach 3: sp_MSforeachtable System Stored Procedure

sp_MSforeachtable is an undocumented system stored procedure which can be used to iterate through each of the tables in a database. In this approach we will get the row counts from each of the tables in a given database in an iterative fashion and display the record counts for all the tables at once.

The T-SQL query below uses the sp_MSforeachtable system stored procedure to iterate through each of the tables to capture the row count for all the tables in a database.

DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
INSERT INTO @TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName], COUNT(*) [RowCount] FROM ?' ;
SELECT [TableName], [RowCount]
FROM @TableRowCounts
ORDER BY [TableName]
GO

Below are the highlights of this approach:

1. This is an iterative approach which captures the row count for each of the individual tables, puts them together and displays the results for all the tables.
2. sp_MSforeachtable is an undocumented system stored procedure.
3. This approach can be used for testing purposes but it is not recommended for use in any production code. sp_MSforeachtable is an undocumented system stored procedure and may change anytime without prior notification from Microsoft.

---

Approach 4: COALESCE() Function

The COALESCE() function is used to return the first non-NULL value/expression among its arguments. In this approach we will build a query to get the row count from each of the individual tables with UNION ALL to combine the results and run the entire query.

The T-SQL query below uses the COALESCE() function to iterate through each of the tables to dynamically build a query to capture the row count from each of the tables (individual COUNT queries combined using UNION ALL) and provides the row counts for all the tables in a database.

DECLARE @QueryString NVARCHAR(MAX) ;
SELECT @QueryString = COALESCE(@QueryString + ' UNION ALL ','')
                      + 'SELECT '
                      + '''' + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + '''' + ' AS [TableName]
                      , COUNT(*) AS [RowCount] FROM '
                      + QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))
                      + '.' + QUOTENAME(sOBJ.name) + ' WITH (NOLOCK) '
FROM sys.objects AS sOBJ
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
ORDER BY SCHEMA_NAME(sOBJ.schema_id), sOBJ.name ;
EXEC sp_executesql @QueryString
GO

Below are the highlights of this approach:

1. Can be used in cases where the number of tables is huge like say few hundred tables. This query can be modified to capture the row counts from a set of tables at one time instead of all the tables which might otherwise put a lot of load on the system.
2. Can be used even when working with source systems which offer limited privileges such as read-only.

---

Below is sample output from AdventureWorksDW database. Note - the results in your AdventureWorksDW database might vary slightly from what is displayed below due to various reasons like the version of database being used, any changes/data manipulation done on the database for testing purposes, etc.

Next Steps

• Review the scripts/approaches in this tips to see which approach suits the best for your scenario.
• Check out these additional resources:
  • Catalog Views
  • Dynamic Management Views and Functions

SQL Server stored procedure to generate random passwords

The following stored procedure creates strings of random characters based on four parameters that configure the result.

1. LEN - specifies the length of the result (required)
2. MIN - sets the starting ASCII code (optional: defaults to "48": which is the number zero)
3. RANGE - determines the range of how many ASCII characters to include (optional: defaults to "74"  (48 + 74 = 122) where 122 is a lowercase "z")
4. EXCLUDE - a string of characters to exclude from the final output (optional: defaults include zero, capital "O", and these punctuation marks :;<=>?@[]`^\/)

Here is the stored procedure:

create proc [dbo].uspRandChars
    @len int,
    @min tinyint = 48,
    @range tinyint = 74,
    @exclude varchar(50) = '0:;<=>?@O[]`^\/',
    @output varchar(50) output
as 
    declare @char char
    set @output = ''
 
    while @len > 0 begin
       select @char = char(round(rand() * @range + @min, 0))
       if charindex(@char, @exclude) = 0 begin
           set @output += @char
           set @len = @len - 1
       end
    end
;
go 

To use the stored procedure issue commands such as the following. 

declare @newpwd varchar(20)
-- all values between ASCII code 48 - 122 excluding defaults
exec [dbo].uspRandChars @len=8, @output=@newpwd out
select @newpwd
-- all lower case letters excluding o and l
exec [dbo].uspRandChars @len=10, @min=97, @range=25, @exclude='ol', @output=@newpwd out
select @newpwd
-- all upper case letters excluding O
exec [dbo].uspRandChars @len=12, @min=65, @range=25, @exclude='O', @output=@newpwd out
select @newpwd
-- all numbers between 0 and 9
exec [dbo].uspRandChars @len=14, @min=48, @range=9, @exclude='', @output=@newpwd out
select @newpwd 

Here is sample output from the above commands: 

Next Steps

• Unsure what ASCII codes to use, refer to this list of ASCII codes
• See where this procedure can be used in your application. Some examples include users inviting their friends or generating a new password by request or as a temporary replacement.
• Determine your environment's security policy including password length and valid characters.
• Implement your password policy in the parameters.

Scripting triggers in Microsoft SQL Server

By default when you use the Script Wizard in Microsoft SQL Server Management Studio to script objects in a database, triggers are note included.

You can include triggers if you [Include all objects] and [Set triggers= true] during the Script Wizard.

To start the script wizard, right click the database that contains the triggers.

Choose Tasks > Generate Scripts…

Check the [Script all objects in the selected database]

Set the [Script Triggers = True]

The generate script will now contain all triggers in the database.

It will not generate drop statements for the triggers

So you should at [drop trigger] if it exists, before executing the generated create trigger statements:

For example the script wizard generates:

CREATE TRIGGER [trd_Order] on [Order]
AFTER DELETE
AS

    INSERT Audit_Order
    SELECT *, 'V',0,getdate()
    ,    null
    ,    null
     FROM deleted
END
GO

Then you should change this to:

IF OBJECT_ID ('trd_Order', 'TR') IS NOT NULL
BEGIN
   DROP TRIGGER trd_Order
END

CREATE TRIGGER [trd_Order] on [Order]
AFTER DELETE
AS

    INSERT Audit_Order
    SELECT *, 'V',0,getdate()
    ,    null
    ,    null
     FROM deleted
END
GO