Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

SQL Server2005, Simple question

Posted on 2008-10-24
Last Modified: 2012-05-05
Need a sql\query to find all table name with 0 records on a database.
I need to list all the table name which has 0 records on a database.

Question by:onebite2
  • 3
  • 3
LVL 39

Expert Comment

ID: 22798317
sp_msforeachtable 'select '?' where not exists (select top 1 * from [?])'

Author Comment

ID: 22798510
I get a syntax error.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '?'.
LVL 39

Expert Comment

ID: 22798550
forgot to escape my quotes around the first ?

sp_msforeachtable 'select ''?'' where not exists (select top 1 * from [?])'
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud


Author Comment

ID: 22798699
I'm not sure what bradon wants me to do.. may be i'm stupid but here is my questions once again.
Need list of tables which has zero records or no records.
I have huge database with almost 700 tables and i'm trying to find out tables with no data.
May be i did something wrong but When i execute the above sql i dont get tables with no data.

LVL 39

Accepted Solution

BrandonGalderisi earned 50 total points
ID: 22798735
Sorry again.  the ? in sp_msforeachtable includes the schema and brackets.  And admittedly, the format may not be desirable.

This updated query will be in a better format and WORK!

create table #HasNoData (TableName sysname)
insert into #HasNoData
exec sp_msforeachtable 'select ''?'' where not exists (select top 1 * from ?)'
select * from #HasNoData
drop table #HasNoData

Author Comment

ID: 22798804
thanks and got it now.

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Order by but want it in specific order 2 34
Insert from Stored Procedure where some field/s > 0 7 45
Query 14 57
Need help in debugging a UDF results 7 17
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question