SQL Server2005, Simple question

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
onebite2Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
BrandonGalderisiConnect With a Mentor Commented:
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
 
BrandonGalderisiCommented:
sp_msforeachtable 'select '?' where not exists (select top 1 * from [?])'
0
 
onebite2Author Commented:
I get a syntax error.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '?'.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
BrandonGalderisiCommented:
forgot to escape my quotes around the first ?

sp_msforeachtable 'select ''?'' where not exists (select top 1 * from [?])'
0
 
onebite2Author Commented:
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
 
onebite2Author Commented:
thanks and got it now.
0
All Courses

From novice to tech pro — start learning today.