Database list having table count is 0

krunal pande

8y860

Hi team I have one table having name master_test in which there are 2 column database name and table name. In database name there are all database name of server and table name is rawdata in all database. Now I want list of database having rawdata table having no record (0 record). Please suggest simple way for the same Thanx & Regards, Krunal Pande

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Shahriar Nikkhah8y ago
--- USE sp_spaceUsed function
Set statistics time on
CREATE TABLE ##TempTable
(name nvarchar(128)
,rows char(11)
,reserved varchar(18)
,data varchar(18)
,index_size varchar(18)
,unused varchar(18)
)
declare @UserTableName nvarchar(40)
declare UserTableSize cursor for
select rtrim(name) from dbo.sysobjects
where OBJECTPROPERTY(id, N'IsUserTable') = 1 order by name
open UserTableSize
fetch next from UserTableSize into @UserTableName
while @@fetch_status = 0
begin
Insert ##TempTable
exec sp_spaceused @UserTableName
fetch next from UserTableSize into @UserTableName
end
close UserTableSize
deallocate UserTableSize
Select * from ##TempTable
--Drop Table ##TempTable
Set statistics time off
GO
-- https://raresql.com/2013/02/27/sql-server-how-to-run-sp_spaceused-for-all-tables-in-a-database/
Rupesh Kahane8y ago
Please run bellow script .
you will get all tables information with number of rows
(you can modify this according to your requirement)
CREATE TABLE #counts
(
    table_name varchar(255),
    row_count int
)

EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC
DROP TABLE #counts