예제 #1
0
        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]}";
        }
예제 #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;
        }