public static string GetFilesInFileGroup(DatabaseInfo.VersionNumber version, FileGroup filegroup) { if (version == DatabaseInfo.VersionNumber.SQLServer2000) return GetFilesInFileGroup2000(filegroup); if (version == DatabaseInfo.VersionNumber.SQLServer2005) return GetFilesInFileGroup2005(filegroup); if (version == DatabaseInfo.VersionNumber.SQLServer2008) return GetFilesInFileGroup2008(filegroup); return ""; }
public static string Get(DatabaseInfo.VersionNumber version) { if (version == DatabaseInfo.VersionNumber.SQLServer2000) return Get2000(); if (version == DatabaseInfo.VersionNumber.SQLServer2005) return Get2005(); if (version == DatabaseInfo.VersionNumber.SQLServer2008) return Get2008(); return ""; }
public static string Get(DatabaseInfo.VersionTypeEnum version) { if (version == DatabaseInfo.VersionTypeEnum.SQLServer2000) return Get2000(); if (version == DatabaseInfo.VersionTypeEnum.SQLServer2005) return Get2005(); //Fall back to highest compatible version return Get2008(); }
public static string GetPrimaryKey(DatabaseInfo.VersionNumber version, Table table) { if (version == DatabaseInfo.VersionNumber.SQLServer2000) return GetPrimaryKey2000(/*table*/); if (version == DatabaseInfo.VersionNumber.SQLServer2005) return GetPrimaryKey2005(); if (version == DatabaseInfo.VersionNumber.SQLServer2008) return GetPrimaryKey2008(); return ""; }
public static string GetUniqueKey(DatabaseInfo.VersionTypeEnum version) { if (version == DatabaseInfo.VersionTypeEnum.SQLServer2005) return GetUniqueKey2005(); if (version == DatabaseInfo.VersionTypeEnum.SQLServer2008 || version == DatabaseInfo.VersionTypeEnum.SQLServer2008R2) return GetUniqueKey2008(); //Fall back to highest compatible version return GetUniqueKeyAzure(); }
public static string Get(DatabaseInfo.VersionTypeEnum version, Database databaseSchema) { if (version == DatabaseInfo.VersionTypeEnum.SQLServer2005) return Get2005(databaseSchema); if (version == DatabaseInfo.VersionTypeEnum.SQLServer2008) return Get2008(databaseSchema); if (version == DatabaseInfo.VersionTypeEnum.SQLServer2008R2) return Get2008R2(databaseSchema); if (version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10) return GetAzure(databaseSchema); return ""; }
public static string GetTableDetail(DatabaseInfo.VersionTypeEnum version) { if (version == DatabaseInfo.VersionTypeEnum.SQLServer2000) return GetTableDetail2000(); if (version == DatabaseInfo.VersionTypeEnum.SQLServer2005) return GetTableDetail2005(); if (version == DatabaseInfo.VersionTypeEnum.SQLServer2008 || version == DatabaseInfo.VersionTypeEnum.SQLServer2008R2) return GetTableDetail2008(); //Fall back to highest compatible version return GetTableDetailAzure(); }
public static string GetView(DatabaseInfo.VersionTypeEnum version) { if (version == DatabaseInfo.VersionTypeEnum.SQLServer2000 || version == DatabaseInfo.VersionTypeEnum.SQLServer2005 || version == DatabaseInfo.VersionTypeEnum.SQLServer2008 || version == DatabaseInfo.VersionTypeEnum.SQLServer2008R2) return GetViewSql2008(); //Fall back to highest compatible version return GetViewSqlAzure(); }
public static string GetPrimaryKey(DatabaseInfo.VersionTypeEnum version, Table table) { if (version == DatabaseInfo.VersionTypeEnum.SQLServer2000) return GetPrimaryKey2000(table); if (version == DatabaseInfo.VersionTypeEnum.SQLServer2005) return GetPrimaryKey2005(); if (version == DatabaseInfo.VersionTypeEnum.SQLServer2008 || version == DatabaseInfo.VersionTypeEnum.SQLServer2008R2) return GetPrimaryKey2008(); //Fall back to highest compatible version return GetPrimaryKeyAzure(); }
public static string GetDatabaseProperties(DatabaseInfo.VersionNumber version, Database databaseSchema) { switch (version) { case DatabaseInfo.VersionNumber.SQLServer2000: case DatabaseInfo.VersionNumber.SQLServer2005: case DatabaseInfo.VersionNumber.SQLServer2008: return string.Format("SELECT DATABASEPROPERTYEX('{0}','IsFulltextEnabled') AS IsFullTextEnabled, DATABASEPROPERTYEX('{0}','Collation') AS Collation, cmptlevel AS CompatibilityLevel from master..sysdatabases where name='{0}'", databaseSchema.Name); break; default: return string.Empty; break; } }
public static string GetDatabases(DatabaseInfo.VersionNumber version) { switch (version) { case DatabaseInfo.VersionNumber.SQLServer2000: return "SELECT name, dbid,cmptlevel FROM master.dbo.sysdatabases ORDER BY Name"; break; case DatabaseInfo.VersionNumber.SQLServer2005: case DatabaseInfo.VersionNumber.SQLServer2008: return "SELECT name, database_id,compatibility_level FROM sys.databases ORDER BY Name"; break; default: return string.Empty; } }
private static void FillColumnsDependencies(DatabaseInfo.VersionNumber DatabaseVersion,SchemaList<UserDataType, Database> types, string connectionString) { if (types == null) throw new ArgumentNullException("types"); using (SqlConnection conn = new SqlConnection(connectionString)) { using (SqlCommand command = new SqlCommand((DatabaseVersion==DatabaseInfo.VersionNumber.SQLServer2000?GetSQLColumnsDependencis2000(): GetSQLColumnsDependencis()), conn)) { conn.Open(); command.CommandTimeout = 0; using (SqlDataReader reader = command.ExecuteReader()) { while (reader.Read()) { types[reader["TypeName"].ToString()].Dependencys.Add(new ObjectDependency(reader["TableName"].ToString(), reader["ColumnName"].ToString(), ConvertType.GetObjectType(reader["Type"].ToString()))); } } } } }
private static string GetSQL(DatabaseInfo.VersionTypeEnum version, SqlOption options) { string sql = ""; sql += "SELECT T.object_id, O.type AS ObjectType, ISNULL(CONVERT(varchar,AM.execute_as_principal_id),'CALLER') as ExecuteAs, AF.name AS assembly_name, AM.assembly_class, AM.assembly_id, AM.assembly_method, T.type, CAST(ISNULL(tei.object_id,0) AS bit) AS IsInsert, CAST(ISNULL(teu.object_id,0) AS bit) AS IsUpdate, CAST(ISNULL(ted.object_id,0) AS bit) AS IsDelete, T.parent_id, S.name AS Owner,T.name,is_disabled,is_not_for_replication,is_instead_of_trigger "; sql += "FROM sys.triggers T "; sql += "INNER JOIN sys.objects O ON O.object_id = T.parent_id "; sql += "INNER JOIN sys.schemas S ON S.schema_id = O.schema_id "; sql += "LEFT JOIN sys.trigger_events AS tei ON tei.object_id = T.object_id and tei.type=1 "; sql += "LEFT JOIN sys.trigger_events AS teu ON teu.object_id = T.object_id and teu.type=2 "; sql += "LEFT JOIN sys.trigger_events AS ted ON ted.object_id = T.object_id and ted.type=3 "; if (version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10) { sql += ",(SELECT null as execute_as_principal_id, null as assembly_class, null as assembly_id, null as assembly_method) AS AM,"; sql += "(SELECT null AS name) AS AF"; } else { sql += "LEFT JOIN sys.assembly_modules AM ON AM.object_id = T.object_id "; sql += "LEFT JOIN sys.assemblies AF ON AF.assembly_id = AM.assembly_id"; } sql += " ORDER BY T.parent_id"; return sql; }
public DatabaseInfo Get(Database database) { DatabaseInfo item = new DatabaseInfo(); using (SqlConnection conn = new SqlConnection(connectioString)) { using (SqlCommand command = new SqlCommand(DatabaseSQLCommand.GetVersion(database), conn)) { conn.Open(); item.Server = conn.DataSource; item.Database = conn.Database; using (SqlDataReader reader = command.ExecuteReader()) { if (reader.Read()) { string versionValue = reader["Version"] as string; try { // used to use the decimal as well when Azure was 10.25 var version = new Version(versionValue); item.VersionNumber = float.Parse(String.Format("{0}.{1}", version.Major, version.Minor), CultureInfo.InvariantCulture); int? edition = null; if (reader.FieldCount > 1 && !reader.IsDBNull(1)) { int validEdition; string editionValue = reader[1].ToString(); if (!String.IsNullOrEmpty(editionValue) && int.TryParse(editionValue, out validEdition)) { edition = validEdition; } } item.SetEdition(edition); } catch (Exception notAGoodIdeaToCatchAllErrors) { bool useDefaultVersion = false; #if DEBUG useDefaultVersion = IsKeyPushedDown(Keys.LShiftKey) && IsKeyPushedDown(Keys.RShiftKey); #endif var exception = new SchemaException( String.Format("Error parsing ProductVersion. ({0})", versionValue ?? "[null]") , notAGoodIdeaToCatchAllErrors); if (!useDefaultVersion) { throw exception; } } } } } using (SqlCommand command = new SqlCommand(DatabaseSQLCommand.Get(item.Version, database), conn)) { using (SqlDataReader reader = command.ExecuteReader()) { if (reader.Read()) { item.Collation = reader["Collation"].ToString(); item.HasFullTextEnabled = ((int)reader["IsFulltextEnabled"]) == 1; } } } } return item; }
private static string GetSQL(DatabaseInfo.VersionTypeEnum version) { string sql = ""; sql += "SELECT ISNULL(CONVERT(varchar,AM.execute_as_principal_id),'CALLER') as ExecuteAs, P.type, AF.name AS assembly_name, AM.assembly_class, AM.assembly_id, AM.assembly_method, P.object_id, S.name as owner, P.name as name "; sql += "FROM sys.procedures P "; sql += "INNER JOIN sys.schemas S ON S.schema_id = P.schema_id "; if (version == DatabaseInfo.VersionTypeEnum.SQLServerAzure10) { sql +=",(SELECT null as execute_as_principal_id, null as assembly_class, null as assembly_id, null as assembly_method) AS AM,"; sql += "(SELECT null AS name) AS AF"; } else { sql += "LEFT JOIN sys.assembly_modules AM ON AM.object_id = P.object_id "; sql += "LEFT JOIN sys.assemblies AF ON AF.assembly_id = AM.assembly_id"; } return sql; }