determining if an SQL database exists etc.

Question

Could someone please give me the simplest possible C# code snippets / SQL queries to determine whether:

* a given SQL database exists,
* a given existing SQL database is accessible, and
* a given table exists within a given existing SQL database?

Thanks for whatever help anyone can provide.

Vijaya Kadiyala · Answer

Hi Bob @DBName is mainly if you are calling from Stored procedure or You declared a variable and assigned the DB name to that variable. If you are passing directly database name to the query then you should use 'DBName'. From tables prespective both are same...But i would recomed to use INFORMATION_SCHEMA.

Bob Lewiston · Answer

Vijaya Kadiyala: Thanks for answering but I'm confused because your syntax differs slightly from that of someone else who I spoke to about this. Could you please comment on the following? To see if a given existing SQL database is accessible your syntax was SELECT * FROM sys.databases WHERE [name] = @DBName AND [state_desc] = 'ONLINE' but another person told me SELECT * FROM sys.databases WHERE name = 'DBName' AND state_desc = 'ONLINE' Did you mean to enclose name and state_desc in braces? Should I use @DBName or 'DBName'? To see if a given table exists within a given existing SQL database your syntax was SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME = 'tablename' but another person told me SELECT tablename FROM sys.tables; Any comments would be appreciated.

Vijaya Kadiyala · Answer

Hi ==> a given SQL database exists select * from sys.databases where name = 'Testing' ==> a given existing SQL database is accessible and SELECT * FROM sys.databases WHERE [name] = @DBName AND [state_desc] = 'ONLINE' ==> a given table exists within a given existing SQL database? SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='tablename' Thanks -- Vijaya Kadiyala www.DotNetVJ.Com

determining if an SQL database exists, etc.