MSSQL.get_schema_info

MSSQL.get_schema_info(names_only=True, include_all=False, column_names=None, verbose=False)[source]

Get the names of existing schemas.

Parameters:

  • names_only (bool) – whether to return only the names of the schema names, defaults to True

  • include_all (bool) – whether to list all the available schemas, defaults to False

  • column_names (None or list) – column names of the returned dataframe if names_only=False; when column_names=None, it defaults to ['schema_name', 'schema_id', 'role']

  • verbose (bool or int) – whether to print relevant information in console, defaults to False

Returns:

schema names

Return type:

list or pandas.DataFrame or None

Examples:

>>> from pyhelpers.dbms import MSSQL

>>> testdb = MSSQL(database_name='testdb')
Creating a database: [testdb] ... Done.
Connecting <server_name>@localhost:1433/testdb ... Successfully.

>>> testdb.get_schema_info()
['dbo']

>>> test_schema_name = 'test_schema'
>>> testdb.create_schema(schema_name=test_schema_name, verbose=True)
Creating a schema: [test_schema] ... Done.
>>> testdb.get_schema_info()
['dbo', 'test_schema']

>>> testdb.get_schema_info(names_only=False, include_all=True)
           schema_name        schema_owner  schema_id
0       db_accessadmin      db_accessadmin      16385
1    db_backupoperator   db_backupoperator      16389
2        db_datareader       db_datareader      16390
3        db_datawriter       db_datawriter      16391
4          db_ddladmin         db_ddladmin      16387
5    db_denydatareader   db_denydatareader      16392
6    db_denydatawriter   db_denydatawriter      16393
7             db_owner            db_owner      16384
8     db_securityadmin    db_securityadmin      16386
9                  dbo                 dbo          1
10               guest               guest          2
11  INFORMATION_SCHEMA  INFORMATION_SCHEMA          3
12                 sys                 sys          4
13         test_schema                 dbo          5

>>> testdb.drop_schema(schema_names='test_schema', verbose=True)
To drop the schema "test_schema" from <server_name>@localhost:1433/testdb
? [No]|Yes: yes
Dropping "test_schema" ... Done.

>>> testdb.get_schema_info()  # None
['dbo']

>>> testdb.drop_database(verbose=True)  # Delete the database "testdb"
To drop the database [testdb] from <server_name>@localhost:1433
? [No]|Yes: yes
Dropping [testdb] ... Done.