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
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;