Could any one please tell me how to code whether a database exists or not in sql azure ?
-
1Downvotes as its the first answer on a search on google for "sql azure check if database exists"Lloyd– Lloyd2012-01-18 19:14:25 +00:00Commented Jan 18, 2012 at 19:14
-
mattgemmell.com/2008/12/08/what-have-you-triedtom redfern– tom redfern2012-07-26 08:13:01 +00:00Commented Jul 26, 2012 at 8:13
Add a comment
|
3 Answers
Have you tried querying the sys.databases table? That should give you what you're looking for. More info here.
Note: You'll want to run this query against the Master database. Otherwise, you'll only see the name of the current database (and Master).
4 Comments
Lloyd
Here is a link relating to Azures security guidelines, which gives a clear sample. msdn.microsoft.com/en-us/library/windowsazure/ff394108.aspx
DSKVP
It mean do we need to use following code that runs aganist master database:
DSKVP
ServerConnection serverConnection = new ServerConnection(connection); Server server = new Server(serverConnection); // after this line, the default database will be switched to Master Database database = server.Databases["MyDatabase"]; // you can still use this database object and server connection to // do certain things against this database, like adding database roles // and users DatabaseRole role = new DatabaseRole(database, "NewRole"); role.Create();
DSKVP
// if you want to execute a script against this database, you have to open // another connection and re-initiliaze the server object server.ConnectionContext.Disconnect(); connection = new SqlConnection(connectionString); serverConnection = new ServerConnection(connection); server = new Server(serverConnection); server.ConnectionContext.ExecuteNonQuery("CREATE TABLE New (NewId int)");
Select count(*) from sysobjects where name = 'testdb' returns 0 if not found.
put the name of your Database and we will edit the script for you .. all you need to do is copy and paste ok..?
here are some additional things you could try as well
Method 1: Use sys.sysdatabases view
IF EXISTS(SELECT * FROM sys.sysdatabases where name=@testdb)
PRINT 'The database exists' else PRINT 'The database does not exist'
Method 2: Use sysdatabases system table from master database
IF EXISTS(SELECT * FROM master..sysdatabases WHERE name=@testdb)
PRINT 'The database exists' else print 'The database does not exist'
Method 3: Using of sp_msforeachdb
--If you dont get a message, the database doesn't exist
DECLARE @sql varchar(1000)SET @sql='if ''?''='''+@ testdb+''' print ''the database exists'''EXEC sp_msforeachdb @sql
Method 4: Using sp_msforeachdb with information_schema.schemata
--If you dont get a message, the database doesn't exist
DECLARE @sql varchar(1000)
SET @sql='if exists(select * from ?.information_schema.schemata wherecatalog_name='''+@ testdb+''') print ''the database exists'''
EXEC sp_msforeachdb @sql
6 Comments
MethodMan
What do you mean it will not work..? what is your table name..?
DSKVP
Thank you for your reply. But i think it work for checking the table exists or not. But i want to check whether database works or not
DSKVP
sorry ....But i think it work for checking the table exists or not. But i want to check whether database exists or not
MethodMan
what is the name of the Database..?
DSKVP
name of the database is testdb
|
