A simple proc to display the number of records in each table in a DB SQL 2017 and above

 GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: KrishanG

-- Create date: 23/10/2023

-- Description: Check if the desired tables get populated with a set of records

-- =============================================

Create PROCEDURE spShowTableRecords 

@tableSchemaName varchar(200)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

declare @SQL varchar(max)

  SELECT @SQL=STRING_AGG( 'select '''+table_name+''' as TableName,count(*) as NoOfRecords  from '+TABLE_SCHEMA+'.'+ table_name, ' union ') FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND TABLE_SCHEMA=@tableSchemaName

  --print @SQL

  exec (@SQL);

   

END

GO