MSSQL.get_schema_info

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

Retrieve information about existing schemas.

Parameters:

  • names_only (bool) – Whether to return only the names of the schemas; defaults to True.

  • include_all (bool) – Whether to include all available schemas; defaults to False.

  • column_names (list | None) – Column names of the returned dataframe if names_only=False; defaults to ['schema_name', 'schema_id', 'role'] if column_names=None.

  • verbose (bool | int) – Whether to print relevant information to the console; defaults to False.

Returns:

Names of schemas, a dataframe with schema information, or None if no information is retrieved.

Return type:

list | pandas.DataFrame | 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.