internal static string CreateDatabaseExistsScript(string databaseName)
{
SqlDdlBuilder sqlDdlBuilder = new SqlDdlBuilder();
sqlDdlBuilder.AppendSql("IF db_id(");
sqlDdlBuilder.AppendStringLiteral(databaseName);
sqlDdlBuilder.AppendSql(") IS NOT NULL SELECT 1 ELSE SELECT Count(*) FROM sys.databases WHERE [name]=");
sqlDdlBuilder.AppendStringLiteral(databaseName);
return(sqlDdlBuilder.unencodedStringBuilder.ToString());
}
internal static string CreateCountDatabasesBasedOnFileNameScript(string databaseFileName, bool useDeprecatedSystemTable)
{
var builder = new SqlDdlBuilder();
builder.AppendSql("SELECT Count(*) FROM ");
if (useDeprecatedSystemTable)
{
builder.AppendSql("sysdatabases");
}
if (!useDeprecatedSystemTable)
{
builder.AppendSql("sys.master_files");
}
builder.AppendSql(" WHERE [");
if (useDeprecatedSystemTable)
{
builder.AppendSql("filename");
}
else
{
builder.AppendSql("physical_name");
}
builder.AppendSql("]=");
builder.AppendStringLiteral(databaseFileName);
return(builder.unencodedStringBuilder.ToString());
}
internal static string CreateGetDatabaseNamesBasedOnFileNameScript(
string databaseFileName,
bool useDeprecatedSystemTable)
{
SqlDdlBuilder builder = new SqlDdlBuilder();
builder.AppendSql("SELECT [d].[name] FROM ");
SqlDdlBuilder.AppendSysDatabases(builder, useDeprecatedSystemTable);
builder.AppendSql(" AS [d] ");
if (!useDeprecatedSystemTable)
{
builder.AppendSql("INNER JOIN sys.master_files AS [f] ON [f].[database_id] = [d].[database_id]");
}
builder.AppendSql(" WHERE [");
if (useDeprecatedSystemTable)
{
builder.AppendSql("filename");
}
else
{
builder.AppendSql("f].[physical_name");
}
builder.AppendSql("]=");
builder.AppendStringLiteral(databaseFileName);
return(builder.unencodedStringBuilder.ToString());
}
internal static string CreateDatabaseExistsScript(string databaseName)
{
// This handles non-SQL Azure servers by using db_id() which will return a non null value for
// a database on the server even if the login doesn't have "view any database" permission.
// On SQL Azure db_id() only works for the current database, which means it will never work here
// because the current database is master. In this case we fall back to the query from sys.databases.
// This doesn't work for non-SQL Azure if the login does not have 'view any database'
// permission, but that case was covered by db_id(). On SQL Azure there are no server class
// securables, which means it is not possible to deny the login 'view any database' so in theory
// if the user can connect to master then the sys.databases query will work.
var builder = new SqlDdlBuilder();
builder.AppendSql("IF db_id(");
builder.AppendStringLiteral(databaseName);
builder.AppendSql(") IS NOT NULL SELECT 1 ELSE SELECT Count(*) FROM sys.databases WHERE [name]=");
builder.AppendStringLiteral(databaseName);
return(builder.unencodedStringBuilder.ToString());
}
internal static string CreateDatabaseExistsScript(string databaseName, bool useDeprecatedSystemTable)
{
var builder = new SqlDdlBuilder();
builder.AppendSql("SELECT Count(*) FROM ");
AppendSysDatabases(builder, useDeprecatedSystemTable);
builder.AppendSql(" WHERE [name]=");
builder.AppendStringLiteral(databaseName);
return(builder.unencodedStringBuilder.ToString());
}
internal static string SetDatabaseOptionsScript(SqlVersion sqlVersion, string databaseName)
{
if (sqlVersion < SqlVersion.Sql9)
{
return(string.Empty);
}
SqlDdlBuilder sqlDdlBuilder = new SqlDdlBuilder();
sqlDdlBuilder.AppendSql("if serverproperty('EngineEdition') <> 5 execute sp_executesql ");
sqlDdlBuilder.AppendStringLiteral(SqlDdlBuilder.SetReadCommittedSnapshotScript(databaseName));
return(sqlDdlBuilder.unencodedStringBuilder.ToString());
}
internal static string SetDatabaseOptionsScript(SqlVersion sqlVersion, string databaseName)
{
if (sqlVersion < SqlVersion.Sql9)
{
return(String.Empty);
}
var builder = new SqlDdlBuilder();
// Set READ_COMMITTED_SNAPSHOT ON, if SQL Server 2005 and up, and not SQLAzure.
builder.AppendSql("if serverproperty('EngineEdition') <> 5 execute sp_executesql ");
builder.AppendStringLiteral(SetReadCommittedSnapshotScript(databaseName));
return(builder.unencodedStringBuilder.ToString());
}
