public List <MsSqlTables> GetSqlDatabaseTables(string server, string instance, string database)
{
//var db = database.Split('.');
var tables = new List <MsSqlTables>();
string tbschema = string.Empty;
string table = string.Empty;
var connection = $"server={instance}; database={database}; Integrated Security=SSPI;";
try
{
using (var con = new SqlConnection(connection))
{
con.Open();
DataTable schema = con.GetSchema("Tables");
//List<string> TableNames = new List<string>();
foreach (DataRow row in schema.Rows)
{
// row(0) = database
// row(1) = schema
tbschema = row[1].ToString();
// row(2) = table name
table = row[2].ToString();
// row(3) = table type
var result = new MsSqlTables()
{
Server = server,
Name = $"{instance}.{database}.{tbschema}.{table}",
Instance = instance,
Database = database,
//Table = $"{db[0]}.{row[0]}.{row[1]}.{row[2]}:{row[3]}"
Table = $"{tbschema}.{table}",
RecordCount = SqlRecordCount(con, database, tbschema, table)
};
tables.Add(result);
}
}
}
catch (Exception ex)
{
var exresult = new MsSqlTables()
{
Server = server,
Name = $"{instance}.{database}.{tbschema}.{tbschema}.{table}.$Error",
Instance = instance,
Database = database,
Table = $"{tbschema}.{table}",
Status = ex.Message
};
tables.Add(exresult);
}
return(tables); // $"{db[0]}.{row[0]}.{row[1]}.{row[2]}\t{row[3]}";
}
//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;
}