Q

Listing database tables without records

SQL Server Integration/Development Expert Joe Toscano shares method to get a more flexible solution from your query.

I created one dynamic SQL Server query to count the number of rows in a table. My aim is to list out the tables which have no records in a database. The query is as follows:
declare @strsql varchar(100)
declare @tablename varchar(50)
@tablename='table123'

@strsql='select count(*) from ' + @tablename

 exec(@strsql)
I am getting the output but I can't store this value in a variable for checking. I want to check like this:
/* @countvariable=0
print(@tablename)*/
If there is any other solution?
You can make your solution more flexible by grabbing the table names from the sysobjects table:
declare @strsql varchar(256)
create table #emptytables  (tablename varchar(128), table_rowcount int)

select @strsql='select distinct o.name as TableName, x.rowcnt as Table_RowCount
 from  sysobjects o
  inner join sysindexes x 
 on o.id = x.id 
 where x.rowcnt =  0 and
 o.type = ''U'''

insert #emptytables (TableName, Table_rowcount) exec (@strsql)
select * from #emptytables
drop table #emptytables
This was first published in May 2006

Dig deeper on SQL Server Database Modeling and Design

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close