determining if an SQL database exists, etc.

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.

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.

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