Exemplo n.º 1
0
        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);
                        }
                    }
            }
        }
Exemplo n.º 2
0
        //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;
        }