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; }