GET Rowcount and min datetimestamp from all the numeric schemas inside SQL Database

The below stored procedure using a dynamic sql with cursor to loop all the tablenames present under the numeric schema and get the rowcount and min datetimestamp of all the tables present under the database. you can change your logic differently as per your requirement as this is the basic syntax of how to loop all the tablenames present under the database with a different condition and insert in to table variable.

 

 

CREATE  PROCEDURE [dbo].[ROWCOUNT_MINCREATETIME]

AS
BEGIN

DECLARE @tableName NVARCHAR(600);

DECLARE @cmd nvarchar(max);SET NOCOUNT ON;
–exec ROWCOUNT_MINCREATETIME
Declare @MainTableinsert TABLE

( Tablename varchar(100),  CountofRows bigint,  MinCreatedDate Datetime)

DECLARE CUR_TABLE CURSOR FOR SELECT    QUOTENAME(t1.name)  +’.’+ QUOTENAME(t2.name) as tablename FROM sys.schemas t1INNER JOIN sys.tables t2 ON t2.schema_id = t1.schema_id where ISNUMERIC(t1.name)=1
OPEN CUR_TABLE

FETCH NEXT FROM CUR_TABLE INTO @tableName
WHILE @@FETCH_STATUS = 0

BEGIN

set @cmd=’Select ”’+ @tablename +”’ as tablename, count(*) as ”Count of rows” , min(CreatedDate) as ” min created date” from ‘+ @tablename +’ (nolock) where CreatedDate < DATEADD(m, -6, current_timestamp)  ‘

INSERT INTO @MainTableinsert

EXEC [dbo].[sp_executesql] @cmd;
FETCH NEXT FROM CUR_TABLE INTO @tableName;
END;
CLOSE CUR_TABLE

DEALLOCATE CUR_TABLE
SELECT * from @MainTableinsert ORDER BY CountofRows DESC
END

 

GO