Solved

SQL Server2005, Simple question

Posted on 2008-10-24
6
177 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

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…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

831 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