Check if database exist...

Friends, today I have share one of my useful database script which I had implemented in Installshield code to check the existence of a database.

Often we come across scenario, where we either need to create a new database if it does not exist, or to update an existing one. Below script consist of a function named DoesDatabaseExist, which will make a call to the database using all the credentials and database name. I bet, you will find it very useful.

///////////////////////////////////////////////////////////////////////////////
//
// Function: DoesDatabaseExist
//
// Purpose: This function will determine whether a given database exists.
//
// Arguments: svServerName - The name of the SQL server to run the script on
// svDatabaseName - The name of the SQL database to run the script on
// svDriver - ADO requires this, but for SQL server you just send in "SQL Server"
// svUserName - The system account for SQL server
// svUserPassword - The password for the system account
//
// Usage:
// if (DoesDatabaseExist("SQLServer", "MyDatabase", "SQL Server", "sa", "saPassword") = FALSE) then
//
///////////////////////////////////////////////////////////////////////////////
function BOOL DoesDatabaseExist(svServerName, svDatabaseName, svDriver, svUserName, svUserPassword)
OBJECT pADOConnObj, pADORecordSetObj;
STRING szADOConnObjID, szADORecordSetObjID, szConnString, szSQL;
BOOL bExists;
begin
try
bExists = FALSE;

// Create ADO Connection Object to connect to the SQL server
szADOConnObjID = "ADODB.Connection";
set pADOConnObj = CreateObject(szADOConnObjID);

// Create the SQL string to complete the connection
svDriver = "SQL Server";

if(gbWinAuthLogin == FALSE) then
szConnString = "driver={" + svDriver + "};";
szConnString = szConnString + "server=" + svServerName + ";";
szConnString = szConnString + "uid=" + svUserName + ";";
szConnString = szConnString + "pwd=" + svUserPassword + ";";
szConnString = szConnString + "database=master";
else
szConnString = "driver={" + svDriver + "};";
szConnString = szConnString + "server=" + svServerName + ";";
szConnString = szConnString + "database=master;";
szConnString = szConnString + "Trusted_connection=Yes";
//ConnectionString = "Data Source="+gsDBServer+";Initial Catalog="+gsDBCatalog+"; Integrated Security=True;";

endif;
// Open the ADO Connection
pADOConnObj.Open(szConnString);

// Create ADO Recordset object for the return
szADORecordSetObjID = "ADODB.Recordset";
set pADORecordSetObj = CreateObject(szADORecordSetObjID);

// Set some ADO Recordset properties
pADORecordSetObj.CursorType = 3;
pADORecordSetObj.ActiveConnection = pADOConnObj;

// Create the SQL string to retrieve the database if it exists
szSQL = "Select name from sysdatabases where name='" + svDatabaseName + "'";

// Use the recordset to see if the database exists
pADORecordSetObj.Open(szSQL);
if (pADORecordSetObj.RecordCount = 1) then
bExists = TRUE;
endif;

return bExists;
catch
MessageBoxEx(Err.Description + ":Error occured","",SEVERE);
endcatch;
end;