Schema discovery with mssql-python

The mssql-python cursor class provides nine metadata methods that map to ODBC catalog functions. Use these methods to discover tables, columns, stored procedures, keys, and indexes programmatically. They help you build data-driven applications that adapt to the database schema at runtime, such as migration tools, code generators, or admin dashboards.

Each method returns a cursor that you can iterate to access the results.

Tables

List tables and views in the database:

cursor = conn.cursor()

# List all tables
for row in cursor.tables():
    print(f"{row.table_schem}.{row.table_name} ({row.table_type})")

# Filter by name (supports wildcards % and _)
for row in cursor.tables(table="Product%"):
    print(row.table_name)

# Filter by schema
for row in cursor.tables(schema="Sales"):
    print(row.table_name)

# Filter by type
for row in cursor.tables(tableType="TABLE"):  # Excludes views
    print(row.table_name)

tables() parameters

The following parameters control table discovery:

tables() result columns

The tables() method returns the following columns for each table or view:

Check if a table exists

Verify that a table exists before querying it:

if cursor.tables(table="Product", schema="Production").fetchone():
    print("Product table exists")
else:
    print("Product table not found")

Columns

Retrieve column information for tables:

# All columns in a table
for row in cursor.columns(table="Product", schema="Production"):
    print(f"{row.column_name}: {row.type_name}({row.column_size})")
    print(f"  Nullable: {row.nullable}, Position: {row.ordinal_position}")

# Filter by column name
for row in cursor.columns(table="Product", schema="Production", column="List%"):
    print(row.column_name)

columns() parameters

Filters to refine the column discovery:

columns() result columns

The columns() method returns detailed information about each column:

Stored procedures

Discover stored procedures:

# List all procedures
for row in cursor.procedures():
    print(f"{row.procedure_schem}.{row.procedure_name}")

# Filter by name pattern
for row in cursor.procedures(procedure="Get%"):
    print(row.procedure_name)

procedures() parameters

Filter stored procedures by name or schema:

procedures() result columns

The procedures() method returns metadata for each stored procedure:

Primary keys

Get primary key columns for a table:

for row in cursor.primaryKeys(table="Product", schema="Production"):
    print(f"PK column: {row.column_name} (position {row.key_seq})")
    print(f"Constraint name: {row.pk_name}")

primaryKeys() parameters

Parameters to retrieve primary key information:

primaryKeys() result columns

The primaryKeys() method returns the following information:

Foreign keys

Discover foreign key relationships:

# Foreign keys from a table (outbound references)
for row in cursor.foreignKeys(table="SalesOrderDetail", schema="Sales"):
    print(f"FK {row.fk_name}:")
    print(f"  {row.fktable_name}.{row.fkcolumn_name}")
    print(f"  -> {row.pktable_name}.{row.pkcolumn_name}")

# Foreign keys to a table (inbound references)
for row in cursor.foreignKeys(foreignTable="Product", foreignSchema="Production"):
    print(f"{row.fktable_name} references Product")

foreignKeys() parameters

Specify primary key or foreign key tables to discover relationships:

foreignKeys() result columns

The foreignKeys() method returns the following columns describing relationships:

Indexes and statistics

Get index information for a table:

# All indexes on a table
for row in cursor.statistics(table="Product", schema="Production"):
    if row.index_name:  # Skip table statistics row
        print(f"Index: {row.index_name}")
        print(f"  Column: {row.column_name} (position {row.ordinal_position})")
        print(f"  Unique: {not row.non_unique}")

# Only unique indexes
for row in cursor.statistics(table="Product", schema="Production", unique=True):
    print(f"Unique index: {row.index_name}")

statistics() parameters

Configure index discovery with these filters:

statistics() result columns

The statistics() method returns index and statistics information:

Row identifier columns

Find columns that uniquely identify a row:

for row in cursor.rowIdColumns(table="Product", schema="Production"):
    print(f"Row ID column: {row.column_name} ({row.type_name})")

This method returns the best set of columns to uniquely identify a row, which might be the primary key or a unique index.

Row version columns

Find columns that are automatically updated when any row value changes. Use row version columns for optimistic concurrency control, where you read a row's version, make changes, and then verify the current row version is the same before writing:

for row in cursor.rowVerColumns(table="Product", schema="Production"):
    print(f"Version column: {row.column_name}")

The result typically includes rowversion/timestamp columns used for optimistic concurrency.

Data type information

Get information about supported SQL data types:

# All supported types
for row in cursor.getTypeInfo():
    print(f"{row.type_name}: {row.data_type}")
    print(f"  Max size: {row.column_size}")
    print(f"  Nullable: {row.nullable}")

# Specific type
for row in cursor.getTypeInfo(sqlType=mssql_python.SQL_VARCHAR):
    print(f"VARCHAR max size: {row.column_size}")

getTypeInfo() parameters

Optional parameters to filter supported SQL types:

Security considerations

Example: Generate a schema report

def describe_table(conn, table_name):
    """Generate a schema description for a table."""
    cursor = conn.cursor()
    
    print(f"\n=== {table_name} ===\n")
    
    # Columns
    print("Columns:")
    for col in cursor.columns(table=table_name):
        nullable = "NULL" if col.nullable else "NOT NULL"
        print(f"  {col.column_name}: {col.type_name}({col.column_size}) {nullable}")
    
    # Primary key
    print("\nPrimary Key:")
    pk_cols = cursor.primaryKeys(table=table_name).fetchall()
    if pk_cols:
        pk_names = ", ".join(row.column_name for row in pk_cols)
        print(f"  {pk_cols[0].pk_name}: ({pk_names})")
    else:
        print("  (none)")
    
    # Foreign keys
    print("\nForeign Keys:")
    for fk in cursor.foreignKeys(table=table_name):
        print(f"  {fk.fk_name}: {fk.fkcolumn_name} -> {fk.pktable_name}.{fk.pkcolumn_name}")
    
    # Indexes
    print("\nIndexes:")
    for idx in cursor.statistics(table=table_name):
        if idx.index_name:
            unique = "UNIQUE " if not idx.non_unique else ""
            print(f"  {unique}{idx.index_name}: {idx.column_name}")

# Usage
describe_table(conn, "Product")

Related content

• Execute queries
• Retrieve data
• Cursor management