How to get list of all databases (servers, schemas, objects, etc) from database server?
Last modified:
To get the list of databases, schemas, objects without loading them to the Metadata Container, use the following code:
using (var sqlContext = new SQLContext()) { sqlContext.Assign(queryBuilder1.SQLContext); // load all databases, not only default sqlContext.MetadataContainer.LoadingOptions.LoadDefaultDatabaseOnly = false; // load system databases if needed sqlContext.MetadataContainer.LoadingOptions.LoadSystemObjects = true; using (var databasesList = new MetadataList(sqlContext.MetadataContainer)) { // load databases from the root level (sqlContext.MetadataContainer) databasesList.Load(MetadataType.Database, false); foreach (var db in databasesList) { using (var schemasList = new MetadataList(db) { // load schemas for each database (db) schemasList.Load(MetadataType.Schema, false); // process schemasList.Items } } } }
Or, if you don't need a list of databases, but a list of schemas from all databases, you can simplify this sample as follows:
using (var sqlContext = new SQLContext()) { sqlContext.Assign(queryBuilder1.SQLContext); // load all databases, not only default sqlContext.MetadataContainer.LoadingOptions.LoadDefaultDatabaseOnly = false; // load system objects if needed sqlContext.MetadataContainer.LoadingOptions.LoadSystemObjects = true; using (var schemasList = new MetadataList(sqlContext.MetadataContainer)) { // set the Recursive parameter to true to load items from lower levels schemasList.Load(MetadataType.Schema, true); foreach (var schema in schemasList) { // process each schema } } }
That's it.