Click here to Skip to main content
15,885,366 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Can anyone please help me to write the query: I need a c# query to check whether the database is empty or not.

I check first whether that database exists :

C#
cmd.CommandText = "SELECT dbid FROM master.dbo.sysdatabases where name = '" + databaseName + "'";       
object DbID = cmd.ExecuteScalar();
if(DbID != null) //if database exists 
{
//CHECK IF DATABASE NOT EMPTY , if not empty ,display message already exists.                          
   cmd.CommandText = "select count(*) from INFORMATION_SCHEMA.TABLES";    
   //this is what i have tried but its giving same count for all the database even if there is a empty db.
   object TCount = cmd.ExecuteScalar();
   if(Convert.ToInt32(TCount) > 0) 
   //if database is not empty
   {
         //return result -database exists;
   }
}
Posted
Updated 23-Oct-14 5:33am
v3
Comments
Richard Deeming 23-Oct-14 11:30am    
Define "not empty". Do you mean that the database doesn't contain any tables? Or that the tables don't contain any rows?

1 solution

Try:
SQL
SELECT COUNT(name) FROM sys.Tables
 
Share this answer
 
Comments
OriginalGriff 23-Oct-14 12:22pm    
Normally you give that as part of the connection string, but you can specify it as part of the command:
USE yourDBName; SELECT COUNT(name) FROM sys.Tables
OriginalGriff 24-Oct-14 6:27am    
You're welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900