SQL SERVER TUTORIAL

Monday, July 19, 2021

List out all user-table with total row-records

In script below, We are going to find-out all user table with row-records. 

-------------------------------------------------------------------------------------------------

DECLARE @sqlcmd VARCHAR(8000);
DECLARE @TABLEWISERECORDS TABLE(dbname NVARCHAR(max),name NVARCHAR(50),rowrecords NUMERIC);
SET @sqlcmd = '
USE ^; 
IF ''^'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'', ''Distribution'')
EXEC sp_MSforeachtable @command1="SELECT db_name = DB_NAME(), ''?'' TABLENAME,COUNT(1) ROWRECORDS FROM ? " '
INSERT @TABLEWISERECORDS
EXEC sp_msforeachdb @command1=@sqlcmd,@replacechar = '^'

SELECT * FROM @TABLEWISERECORDS



No comments:

Post a Comment