Today I’ve wrote a simple script to get number of records for all tables in a database and fetch those tables that contains at least one record.
This picture shows an example of result set:
Here is the script:
declare @tempTable
table(tablename
varchar(max),
rowscount int,processed bit)
insert into @tempTable
select TABLE_NAME,0,0 from INFORMATION_SCHEMA.TABLES
where TABLE_CATALOG=‘DEMO’
declare
@currentTable varchar(max)
set
@currentTable=(select top 1 tablename from
@tempTable where processed=0)
declare
@sqlCommand nvarchar(500)
declare @rowsnum int
declare @param nvarchar(500)
set @param= N’@cnt int OUTPUT’;
while (select COUNT(*) from @tempTable where
processed=0)<>0
begin
set
@sqlCommand=N’select
@cnt=count(*) from ‘+@currentTable
EXECUTE sp_executesql @sqlCommand, @param, @cnt=@rowsnum OUTPUT;
update @tempTable
set processed=1,rowscount=@rowsnum
where tablename=@currentTable
set
@currentTable=(select top 1 tablename from
@tempTable where processed=0)
end
select tablename,rowscount from
@tempTable
where rowscount>0
order by rowscount desc
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.