public static IEnumerable <MsSqlTableColumns> GetSqlTableColumns(string server, string instance, string database)
{
const string query = @"SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, ORDINAL_POSITION,
COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE,
DATETIME_PRECISION,
CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, CHARACTER_SET_NAME,
COLLATION_CATALOG, COLLATION_SCHEMA, COLLATION_NAME
FROM INFORMATION_SCHEMA.COLUMNS;";
//var db = database.Split('.');
var connection = $"server={server}\\{instance}; database={database}; Integrated Security=SSPI;";
using (var con = new SqlConnection(connection))
{
con.Open();
//DataTable schema = con.GetSchema("Tables");
//List<string> TableNames = new List<string>();
//string query = "select * from sys.databases where len(owner_sid) > 1;";
using (var cmd = new SqlCommand(query, con))
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var r = new MsSqlTableColumns()
{
TABLE_CATALOG = reader.SafeGetString(0),
TABLE_SCHEMA = reader.SafeGetString(1),
TABLE_NAME = reader.SafeGetString(2),
COLUMN_NAME = reader.SafeGetString(3),
ORDINAL_POSITION = reader.GetInt32(4),
COLUMN_DEFAULT = reader.SafeGetString(5),
IS_NULLABLE = reader.SafeGetBool(6),
DATA_TYPE = reader.SafeGetString(7),
CHARACTER_MAXIMUM_LENGTH = reader.SafeGetInt32(8),
NUMERIC_PRECISION = reader.SafeGetInt32(9),
NUMERIC_PRECISION_RADIX = reader.SafeGetInt32(10),
NUMERIC_SCALE = reader.SafeGetInt32(11),
DATETIME_PRECISION = reader.SafeGetInt32(12),
CHARACTER_SET_CATALOG = reader.SafeGetString(13),
CHARACTER_SET_SCHEMA = reader.SafeGetString(14),
CHARACTER_SET_NAME = reader.SafeGetString(15),
COLLATION_CATALOG = reader.SafeGetString(16),
COLLATION_SCHEMA = reader.SafeGetString(17),
COLLATION_NAME = reader.SafeGetString(18),
Server = server,
Instance = instance
};
r.Path = r.TABLE_CATALOG;
r.Name = $"{r.TABLE_SCHEMA}.{r.TABLE_NAME}.{r.COLUMN_NAME}";
yield return(r);
}
}
}
}
//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;
}