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.