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
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
  • 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 [?])'
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now


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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard 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.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

729 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