OBJECTIVE

System metadata – specifically objects, columns, and column constraints – will be queried using Database Catalogs that both Oracle and MSSQL Server provide. These sections utilize just some, of the many, that are available.

PERSPECTIVE

PREREQUISITES

MSSQL

A database instance (local or remote)
SQL Server Management Studio or Toad for SQL Server

Oracle

A database instance (local or remote)
SQL Developer or Toad for Oracle

BACKGROUND

There are times when you need to find something in the database without knowing what/where it is.

While most IDEs provide this kind of functionality, as the Toad screenshot below demonstrates, it does not have the level of customization that a regular query does.

1. Object Search
2. Name of the object to find
3. Execute the search

There are other scenarios where these queries can be helpful, for example, if you need to do any sort of meta-programming. Maybe your application needs to display some metadata about a table, or it needs to make some decision based on a column’s type, or a backup script needs to query for any new tables to include, etc.

The SQL

At the top of every script is a local variable declaration section. The known (or partially known) information should be filled in and the query will use that to find corresponding data. The specified values can be specific, vague, or blank as the queries were written to utilize pattern matching. The Oracle queries utilize SQL*Plus for it’s convenient bind variable (cursor) printing. Therefore, the Oracle queries will require an IDE with the capability to run them.

Querying Objects

“Objects” are the general items accessible in the database – like tables, keys, etc. It does not provide column information, that info is provided in the next section of queries.

SQL Server and Oracle both expose similar items through their system views named “Object”, there are some differences, but the major stuff is covered.

MSSQL

/************************************************************
** Finds:
**  system tables, foreign key constraints, user tables,
**  service queues, internal tables, primary key constraints
************************************************************/
DECLARE 
  @obj_nm VARCHAR(MAX) = '', --example: 'users' or just ''
  @obj_typ VARCHAR(MAX) = '', --example: 'table' or just ''
  @schema_nm VARCHAR(MAX) = '', --example: 'dbo' or just ''
  @max_rows INT = 250; --change as necessary for more results

SELECT DISTINCT TOP (@max_rows)
  [S].name AS 'schema_name',
  [O].name AS 'object_name',
  [O].type_desc AS 'object_type'
FROM
  SYS.SCHEMAS [S]
INNER JOIN
  SYS.OBJECTS [O]
  ON [O].schema_id = [S].schema_id
WHERE  
  [O].type NOT IN ('S', 'IT')
  AND [O].name LIKE '%' + ISNULL(NULLIF(@obj_nm, ''), [O].name) + '%'
  AND [O].type_desc LIKE '%' + ISNULL(NULLIF(@obj_typ, ''), [O].type_desc) + '%'
  AND [S].name LIKE '%' + ISNULL(NULLIF(@schema_nm, ''), [S].name) + '%'
ORDER BY
  schema_name ASC,
  object_name ASC,
  object_type ASC;

Oracle

/************************************************************
** Finds:
**  clusters, consumer groups, destinations, directories,
**  editions, evaluation contexts, functions, indexes,
**  index partitions, indextypes, java classes, java data,
**  java resources, java sources, job classes, materialized views,
**  operators, packages, package bodies, procedures, programs,
**  schedules, scheduler groups, sequences, synonyms, tables,
**  table partitions, table subpartitions, triggers, types,
**  type bodies, views, windows, xml schemas
************************************************************/
VARIABLE v_crsr REFCURSOR;

DECLARE
  l_obj_nm VARCHAR2(4000) := UPPER(''); --example: 'users' or just ''
  l_obj_typ VARCHAR2(4000) := UPPER(''); --example: 'table' or just ''
  l_owner VARCHAR2(4000) := UPPER(''); --example: ''
  l_max_rows INT := 250; --change as necessary for more results

BEGIN
  OPEN :v_crsr FOR
    SELECT DISTINCT
      owner,
      object_name,
      object_type
    FROM 
      SYS.DBA_OBJECTS
    WHERE
      ROWNUM <= l_max_rows       
      AND object_name LIKE '%' || NVL(l_obj_nm, object_name) || '%'
      AND object_type LIKE '%' || NVL(l_obj_typ, object_type) || '%'
      AND owner LIKE '%' || NVL(l_owner, owner) || '%'
      AND owner NOT IN(
        'APPQOSSYS', 'AUDSYS', 'CTXSYS', 'DBSFWUSER', 'DBSNMP', 'DVF', 
        'DVSYS', 'GSMADMIN_INTERNAL', 'LBACSYS', 'MDSYS', 'OJVMSYS', 
        'OLAPSYS', 'ORACLE_OCM', 'ORDDATA', 'ORDPLUGINS', 'ORDSYS', 
        'OUTLN', 'PUBLIC', 'REMOTE_SCHEDULER_AGENT', 'SI_INFORMTN_SCHEMA',
        'SYS', 'SYSTEM', 'WMSYS', 'XDB'
      )      
    ORDER BY
      owner ASC,
      object_name ASC,
      object_type ASC;
END;

PRINT v_crsr;

Querying Columns

Since the above section did not cover columns – the queries below can be used to obtain that information.

MSSQL

/************************************************************
** Finds:
**  Column information matching the specified name
************************************************************/
DECLARE 
  @col_nm VARCHAR(MAX) = '', --example: 'username' or just ''
  @max_rows INT = 250; --change as necessary for more results

SELECT DISTINCT TOP (@max_rows)
  [S].name AS 'schema_name',
  [O].name AS 'table_name',
  [C].name AS 'column_name',
  [T].name AS 'data_type',
  [C].max_length AS 'data_length',
  [C].is_nullable,
  [C].column_id
FROM   
  SYS.SCHEMAS [S]
INNER JOIN
  SYS.OBJECTS [O]
  ON [O].schema_id = [S].schema_id
INNER JOIN
  SYS.COLUMNS [C]
  ON [C].object_id = [O].object_id
INNER JOIN
  SYS.TYPES [T]
  ON [T].system_type_id = [C].system_type_id
WHERE  
  [T].name <> 'sysname' 
  AND [O].type = 'U'        
  AND [C].name LIKE '%' + ISNULL(NULLIF(@col_nm, ''), [C].name) + '%'
ORDER BY
  schema_name ASC,
  table_name ASC,
  column_id ASC;

Oracle

/************************************************************
** Finds:
**  Column information matching the specified name
************************************************************/
VARIABLE v_crsr REFCURSOR;

DECLARE
  l_col_nm VARCHAR2(4000) := UPPER(''); --example: 'username' or just ''
  l_max_rows INT := 250; --change as necessary for more results

BEGIN
  OPEN :v_crsr FOR
    SELECT DISTINCT
      owner,
      table_name,
      column_name,
      data_type,
      data_length,
      nullable,
      column_id
    FROM 
      SYS.DBA_TAB_COLUMNS
    WHERE 
      ROWNUM <= l_max_rows
      AND column_name LIKE '%' || NVL(l_col_nm, column_name) || '%'
      AND owner NOT IN(
        'APPQOSSYS', 'AUDSYS', 'CTXSYS', 'DBSFWUSER', 'DBSNMP', 'DVF', 
        'DVSYS', 'GSMADMIN_INTERNAL', 'LBACSYS', 'MDSYS', 'OJVMSYS', 
        'OLAPSYS', 'ORACLE_OCM', 'ORDDATA', 'ORDPLUGINS', 'ORDSYS', 
        'OUTLN', 'PUBLIC', 'REMOTE_SCHEDULER_AGENT', 'SI_INFORMTN_SCHEMA', 
        'SYS', 'SYSTEM', 'WMSYS', 'XDB'       
      )    
    ORDER BY
      owner ASC
      table_name ASC,
      column_id ASC;
END;

PRINT v_crsr;

Querying Column Constraints

This section is the most complicated out of the three. Both the MSSQL and Oracle queries use a Common Table Expression (CTE) to get one set of column&keys (primary or foreign) and then using that it gets the other associated side (primary or foreign).

MSSQL

/************************************************************
** Finds:
**  PK and FK columns with their relationships and constraints
************************************************************/
DECLARE 
  @pk_col_nm VARCHAR(MAX) = '', --example: 'account' or just ''
  @fk_col_nm VARCHAR(MAX) = '', --example: ''
  @max_rows INT = 250; --change as necessary for more results

WITH PK_COL_CTE AS(
  SELECT DISTINCT
    [O].object_id AS 'pk_obj_id',
    [C].column_id AS 'pk_col_id',
    [S].name AS 'pk_schema_nm',
    [O].name AS 'pk_tbl_nm',
    [C].name AS 'pk_col_nm',
    [KC].name AS 'pk_constraint_nm'
  FROM
    SYS.SCHEMAS [S]
  INNER JOIN
    SYS.KEY_CONSTRAINTS [KC]
    ON [KC].schema_id = [S].schema_id
  INNER JOIN         
    SYS.INDEX_COLUMNS [IC]
    ON [IC].index_id = [KC].unique_index_id    
  INNER JOIN
    SYS.COLUMNS [C]    
    ON [C].column_id = [IC].index_column_id
  INNER JOIN
    SYS.OBJECTS [O]
    ON [O].object_id = [C].object_id 
    AND [O].object_id = [IC].object_id
    AND [O].object_id = [KC].parent_object_id    
  WHERE
    [KC].type = 'PK' 
    AND [O].type = 'U'
)
SELECT DISTINCT TOP (@max_rows) 
  [PCC].pk_schema_nm + '.' + [PCC].pk_tbl_nm + '.' + [PCC].pk_col_nm AS 'pk_column',
  [PCC].pk_constraint_nm AS 'pk_constraint',
  '=>' AS 'referenced by',
  [S].name + '.' + [O2].name + '.' + [C].name AS 'fk_column',
  [O1].name AS 'fk_constraint'
FROM 
  PK_COL_CTE [PCC]
INNER JOIN
  SYS.FOREIGN_KEY_COLUMNS [FKC]
  ON [FKC].referenced_object_id = [PCC].pk_obj_id
INNER JOIN
  SYS.COLUMNS [C]
  ON [C].column_id = [FKC].parent_column_id
  AND [C].object_id = [FKC].parent_object_id
INNER JOIN
  SYS.OBJECTS [O1] --constraint
  ON [O1].object_id = [FKC].constraint_object_id
INNER JOIN              
  SYS.OBJECTS [O2] --table
  ON [O2].object_id = [O1].parent_object_id
INNER JOIN
  SYS.SCHEMAS [S]
  ON [S].schema_id = [O2].schema_id  
WHERE         
  [PCC].pk_col_nm LIKE '%' + ISNULL(NULLIF(@pk_col_nm, ''), [PCC].pk_col_nm) + '%'
  AND [C].name LIKE '%' + ISNULL(NULLIF(@fk_col_nm, ''), [C].name) + '%'
ORDER BY
  pk_column ASC,
  pk_constraint ASC,
  fk_column ASC,
  fk_constraint ASC;

Oracle

/************************************************************
** Finds:
**  PK and FK columns with their relationships and constraints
************************************************************/
VARIABLE v_crsr REFCURSOR;

DECLARE
  l_pk_col_nm VARCHAR2(4000) := UPPER(''); --example: 'account' or just ''
  l_fk_col_nm VARCHAR2(4000) := UPPER(''); --example: ''
  l_max_rows INT := 250; --change as necessary for more results

BEGIN
  OPEN :v_crsr FOR        
    WITH FK_COL_CTE AS(
      SELECT DISTINCT
        dcc.owner,
        dcc.table_name,
        dcc.column_name,
        dc.constraint_name,
        dc.constraint_type,
        dc.r_constraint_name
      FROM 
        SYS.DBA_CONS_COLUMNS dcc
      INNER JOIN
        SYS.DBA_CONSTRAINTS dc
        ON dc.owner = dcc.owner
        AND dc.constraint_name = dcc.constraint_name
        AND dc.table_name = dcc.table_name                
      WHERE  
        dc.constraint_type = 'R'
        AND dcc.owner NOT IN(
          'APPQOSSYS', 'AUDSYS', 'CTXSYS', 'DBSFWUSER', 'DBSNMP', 
          'DVF', 'DVSYS', 'GSMADMIN_INTERNAL', 'LBACSYS', 'MDSYS', 
          'OJVMSYS', 'OLAPSYS', 'ORACLE_OCM', 'ORDDATA', 'ORDPLUGINS', 
          'ORDSYS', 'OUTLN', 'PUBLIC', 'REMOTE_SCHEDULER_AGENT', 
          'SI_INFORMTN_SCHEMA', 'SYS', 'SYSTEM', 'WMSYS', 'XDB'
        )  
    )    
    SELECT 
      dcc.owner || '.' || dcc.table_name || '.' || dcc.column_name AS "pk_column",
      dcc.constraint_name AS "pk_constraint",
      '=>' AS "referenced by",
      fcc.owner || '.' || fcc.table_name || '.' || fcc.column_name AS "fk_column",
      fcc.constraint_name AS "fk_constraint"
    FROM 
      FK_COL_CTE fcc
    INNER JOIN
      SYS.DBA_CONS_COLUMNS dcc
      ON dcc.constraint_name = fcc.r_constraint_name
    WHERE 
      ROWNUM <= l_max_rows                      
      AND dcc.column_name LIKE '%' || NVL(l_pk_col_nm, dcc.column_name) || '%'
      AND fcc.column_name LIKE '%' || NVL(l_fk_col_nm, fcc.column_name) || '%'
    ORDER BY
      dcc.owner ASC,
      dcc.table_name ASC,
      dcc.column_name ASC,
      dcc.constraint_name ASC;
END;

PRINT v_crsr;

Conclusion

This article has only scratched the surface for all the kinds of metadata that MSSQL and Oracle expose. You can find more documentation at MSDN and Oracle for their respective Database Catalogs.