Solved

SQL Server2005, Simple question

Posted on 2008-10-24
6
179 Views
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.

Thanks
Cindy
0
Comment
Question by:onebite2
[X]
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
6 Comments
 
LVL 39

Expert Comment

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

Author Comment

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

Expert Comment

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

sp_msforeachtable 'select ''?'' where not exists (select top 1 * from [?])'
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:onebite2
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.

Thanks
Cindy.
0
 
LVL 39

Accepted Solution

by:
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
0
 

Author Comment

by:onebite2
ID: 22798804
thanks and got it now.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

749 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