| Querying Database System Metadata |

by TechRandomness

OBJECTIVE

In this article 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:
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
Other scenarios where these queries can be helpful is 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

The following queries have intentionally verbose joins – using more columns than necessary. This is done to exhibit the relationships (related data) between the various tables/views.
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 the correspondent data. The specified values can be specific or vague – the queries were written to utilize pattern matching and to allow values to be omitted. The “%” and “_” characters can be used as wildcards. For example, “%users%” says to find anything containing the text “users” in it.

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%'
,@obj_typ VARCHAR(MAX) = '' --example: '%table'
,@schema_nm VARCHAR(MAX) = '' --example: 'dbo'
,@max_rows INT = 250 --change as necessary for more results
;

SELECT DISTINCT
TOP (@max_rows) --limiting # of records by using the local var
[S].name AS 'schema_name'
,[O].name AS 'object_name'
,[O].type_desc AS 'object_type'
FROM SYS.OBJECTS [O]
INNER JOIN
SYS.SCHEMAS [S]
ON [O].schema_id = [S].schema_id
WHERE [O].type NOT IN ('S', 'IT') --excluding system schemas
--the clauses below will use what is provided by the local vars
--if nothing was provided the value matches to itself
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
******************************************************************************/

--sql*plus bind variable
VARIABLE v_crsr REFCURSOR;

DECLARE
l_obj_nm VARCHAR2(4000) := UPPER('') --example: 'users%'
;l_obj_typ VARCHAR2(4000) := UPPER('') --example: 'table'
;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 --limiting # of records by using the local var
AND owner NOT IN( --excluding system schemas
'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'
)
--the clauses below will use what is provided by the local vars
--if nothing was provided the value matches to itself
AND owner LIKE NVL(l_owner, owner)
AND object_name LIKE NVL(l_obj_nm, object_name)
AND object_type LIKE NVL(l_obj_typ, object_type)
ORDER BY
owner ASC
,object_name ASC
,object_type ASC
;
END;
/

--sql*plus command to output cursor records
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%'
,@max_rows INT = 250 --change as necessary for more results
;

SELECT DISTINCT
TOP (@max_rows) --limiting # of records by using the local var
[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.OBJECTS [O]
INNER JOIN
SYS.SCHEMAS [S]
ON [O].schema_id = [S].schema_id
INNER JOIN
SYS.COLUMNS [C]
ON [O].object_id = [C].object_id
INNER JOIN
SYS.TYPES [T]
ON [C].system_type_id = [T].system_type_id
WHERE [T].name <> 'sysname' --make sure to just get columns
AND [O].type = 'U' --include only user created columns
--the clause below will use what is provided by the local var
--if nothing was provided the value matches to itself
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
******************************************************************************/

--sql*plus bind variable
VARIABLE v_crsr REFCURSOR;

DECLARE
l_col_nm VARCHAR2(4000) := UPPER('') --example: '%username%'
;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 --limiting # of records by using the local var
AND owner NOT IN( --excluding system schemas
'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'
)
--the clause below will use what is provided by the local var
--if nothing was provided the value matches to itself
AND column_name LIKE NVL(l_col_nm, column_name)
ORDER BY
owner ASC
,table_name ASC
,column_id ASC
;
END;
/

--sql*plus command to output cursor records
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 columns and FK columns with their relationships and constraints
******************************************************************************/

DECLARE @pk_col_nm VARCHAR(MAX) = '' --example: 'account%'
,@fk_col_nm VARCHAR(MAX) = '' --example: ''
,@max_rows INT = 250 --change as necessary for more results
;

--CTE getting the primary key columns and their constraints
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'
,[K].name AS 'pk_constraint_nm'
FROM SYS.KEY_CONSTRAINTS [K]
INNER JOIN
--uses index_columns to get the column associated with the pk constraint
SYS.INDEX_COLUMNS [I]
ON [K].unique_index_id = [I].index_id
AND [K].parent_object_id = [I].object_id
INNER JOIN
SYS.COLUMNS [C]
ON [K].parent_object_id = [C].object_id
AND [I].object_id = [C].object_id
AND [I].index_column_id = [C].column_id
INNER JOIN
SYS.OBJECTS [O]
ON [K].parent_object_id = [O].object_id
AND [I].object_id = [O].object_id
AND [C].object_id = [O].object_id
INNER JOIN
SYS.SCHEMAS [S]
ON [K].schema_id = [S].schema_id
AND [O].schema_id = [S].schema_id
WHERE [K].type = 'PK' --primary key type
AND [O].type = 'U' --include only user created columns
)
--Take the CTE and find the associated foreign key columns and their constraints
SELECT DISTINCT
TOP (@max_rows) --limiting # of records by using the local var
[CTE].pk_schema_nm + '.' + [CTE].pk_tbl_nm + '.' + [CTE].pk_col_nm AS 'pk_column'
,[CTE].pk_constraint_nm AS 'pk_constraint'
,' -> ' AS ' '
,[SCH].name + '.' + [OB2].name + '.' + [COL].name AS 'fk_column'
,[OB1].name AS 'fk_constraint'
FROM PK_COL_CTE [CTE]
INNER JOIN
SYS.FOREIGN_KEY_COLUMNS [FKC]
ON [FKC].referenced_object_id = [CTE].pk_obj_id
INNER JOIN
SYS.COLUMNS [COL]
ON [FKC].parent_column_id = [COL].column_id
AND [FKC].parent_object_id = [COL].object_id
INNER JOIN
SYS.OBJECTS [OB1]
ON [FKC].constraint_object_id = [OB1].object_id
INNER JOIN
SYS.SCHEMAS [SCH]
ON [OB1].schema_id = [SCH].schema_id
INNER JOIN
--joining on objects again is needed since [OB1] gets the constraint object
--this takes the constraint object and gets the the parent table object
SYS.OBJECTS [OB2]
ON [OB1].parent_object_id = [OB2].object_id
WHERE --the clauses below will use what is provided by the local vars
--if nothing was provided the value matches to itself
[CTE].pk_col_nm LIKE ISNULL(NULLIF(@pk_col_nm, ''), [CTE].pk_col_nm)
AND [COL].name LIKE ISNULL(NULLIF(@fk_col_nm, ''), [COL].name)
ORDER BY
pk_column ASC
,pk_constraint ASC
,fk_column ASC
,fk_constraint ASC
;

Oracle:
/******************************************************************************
** Finds:
** PK columns and FK columns with their relationships and constraints
******************************************************************************/

--sql*plus bind variable
VARIABLE v_crsr REFCURSOR;

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

BEGIN
OPEN :v_crsr FOR
--CTE getting the foreign key columns and their constraints
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_TAB_COLUMNS "DTC"
ON "DCC".owner = "DTC".owner
AND "DCC".table_name = "DTC".table_name
AND "DCC".column_name = "DTC".column_name
INNER JOIN
SYS.DBA_CONSTRAINTS "DC"
ON "DCC".owner = "DC".owner
AND "DCC".constraint_name = "DC".constraint_name
AND "DCC".table_name = "DC".table_name
AND "DTC".owner = "DC".owner
AND "DTC".table_name = "DC".table_name
WHERE "DCC".owner NOT IN( --excluding system schemas
'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'
)
AND "DC".constraint_type = 'R'
)
--Take the CTE and find the associated primary key columns and their constraints
SELECT "PK".owner || '.' || "PK".table_name || '.' || "PK".column_name AS "pk_column"
,"PK".constraint_name AS "pk_constraint"
,' -> ' AS " "
,"FK".owner || '.' || "FK".table_name || '.' || "FK".column_name AS "fk_column"
,"FK".constraint_name AS "fk_constraint"
FROM FK_COL_CTE "FK"
INNER JOIN
SYS.DBA_CONS_COLUMNS "PK"
ON "FK".r_constraint_name = "PK".constraint_name
WHERE ROWNUM <= l_max_rows --limiting # of records by using the local var
--the clauses below will use what is provided by the local vars
--if nothing was provided the value matches to itself
AND "PK".column_name LIKE NVL(l_pk_col_nm, "PK".column_name)
AND "FK".column_name LIKE NVL(l_fk_col_nm, "FK".column_name)
ORDER BY
"PK".owner ASC
,"PK".table_name ASC
,"PK".column_name ASC
,"PK".constraint_name ASC
;
END;
/

--sql*plus command to output cursor records
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.