public static IEnumerable <MsSqlDatabases> GetSqlDatabases(string server, string instance)
{
var connection = $"server={server}\\{instance}; database=master; Integrated Security=SSPI;";
using (var con = new SqlConnection(connection))
{
con.Open();
//using (var cmd = new SqlCommand("Select Name from Sys.Databases;", con))
// WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb'); "ReportServer" and "ReportServerTempDB
// WHERE dbid > 4; Le(owner_sid) > 1 // 'ReportServer',
string query = "SELECT * FROM SYS.DATABASES WHERE database_id > 4 AND Name NOT IN ('ReportServerTempDB');";
using (var cmd = new SqlCommand(query, con))
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var result = new MsSqlDatabases()
{
Server = server,
Instance = instance,
Name = reader[0].ToString()
};
//result.Name = $"{result.Database}";
yield return(result); // (instance + "." + reader[0].ToString());
}
}
}
}
//public string Get
// https://stackoverflow.com/questions/1054984/how-can-i-get-column-names-from-a-table-in-sql-server
public void Inventory(string saveToFolder)
{
var InventoryFolder = saveToFolder;
Console.WriteLine($"{Utilities.LogTime()} Begin MsSqlInventory::...");
sqlinstances = MsSqlServers.GetSqlInstanceNames().ToList <MsSqlServers>();
MsSqlServers.ToJsonFile(sqlinstances, InventoryTime, InventoryFolder);
Console.WriteLine($"{Utilities.LogTime()} Completed MsSqlInventory::..:[GetSqlInstanceNames]");
sqlinstances.ForEach(d => { dblist
.AddRange(MsSqlDatabases.GetSqlDatabases(d.Server, d.Name)
.ToList <MsSqlDatabases>()); });
MsSqlDatabases.ToJsonFile(dblist, InventoryTime, InventoryFolder);
Console.WriteLine($"{Utilities.LogTime()} Completed MsSqlInventory::..:[GetSqlDatabases]");
//dblist.ForEach(d => { tblist.AddRange(GetSqlDatabaseTables(d.Server, d.Instance, d.Database).ToList<MsSqlTables>()); });
dblist.ForEach(d => { tblist
.AddRange(MsSqlTables.GetSqlDatabaseTables(d.Server, d.Instance, d.Name)); });
MsSqlTables.ToJsonFile(tblist, InventoryTime, InventoryFolder);
Console.WriteLine($"{Utilities.LogTime()} Completed MsSqlInventory::..:[GetSqlDatabaseTables]");
dblist.ForEach(d => { columns
.AddRange(MsSqlTableColumns.GetSqlTableColumns(d.Server, d.Instance, d.Name)
.ToList <MsSqlTableColumns>()); });
MsSqlTableColumns.ToJsonFile(columns, InventoryTime, InventoryFolder);
Console.WriteLine($"{Utilities.LogTime()} Completed MsSqlInventory::..:[GetSqlTableColumns]");
dblist.ForEach(d => { procedures
.AddRange(MsSqlStoredProcedure.GetSqlStoredProcedures(d.Server, d.Instance, d.Name)
.ToList <MsSqlStoredProcedure>()); });
MsSqlStoredProcedure.ToJsonFile(procedures, InventoryTime, InventoryFolder);
Console.WriteLine($"{Utilities.LogTime()} Completed MsSqlInventory::..:[GetSqlStoredProcedures({procedures.Count()})]");
//Console.WriteLine($"{Utilities.LogTime()} Completed MsSqlInventory::..:[ToJsonFile]");
//var ix = 0;
}
