|
|
|
|
| Re: query to retrieve primary key [message #9692 is a reply to message #9688] |
Tue, 02 December 2003 01:27   |
Nirav Raval
Messages: 2
Registered: December 2003
|
Junior Member |
|
|
Put below query in a .sql file and then run it in sqlplus. Hope this helps.
break on index_name skip 1
col index_name format a30
col uniuenes format a12
col column_name format a30
prompt Indexes for table: &&1
select c.index_name, i.uniqueness, c.column_name
from user_indexes i, user_ind_columns c
where i.index_name = c.index_name
and i.table_name = upper('&&1')
order by c.index_name, c.column_position
/
undef 1
|
|
|
|
| Re: query to retrieve primary key [message #9695 is a reply to message #9688] |
Tue, 02 December 2003 01:56 |
William Robertson
Messages: 1643
Registered: August 2003
Location: London, UK
|
Senior Member |
|
|
There is some overlap between constraints and indexes. I use the following a lot for indexes (save as ind.sql, run using either "@ind tablename" or "@ind indexname"):
<pre style="color: navy;">def table = &1
COL index_name HEA "Index"
COL uniqueness FORMAT A7 HEA "Unique?"
COL index_type FORMAT A17 HEA "Type"
COL status FORMAT A8 HEA "Status"
COL num_rows FORMAT 99,999,990 HEA "Rows"
COL distinct_keys FORMAT 99,999,990 HEA "Distinct|keys"
COL column_name FORMAT A25 HEA "Column name"
cl bre
break on index_name on uniqueness on index_type on status on num_rows on distinct_keys skip1
set term off
store set sqlplus_settings.sql replace
set feed off serverout on term on autoprint on
var output REFCURSOR
DECLARE
k_object_name CONSTANT VARCHAR2(30) := UPPER('&table');
v_object_type user_objects.object_type%TYPE;
v_table_name user_objects.object_name%TYPE := k_object_name;
BEGIN
SELECT object_type INTO v_object_type
FROM user_objects
WHERE object_name = k_object_name
AND object_type IN ('TABLE','INDEX');
IF v_object_type = 'INDEX' THEN
SELECT table_name
INTO v_table_name
FROM user_indexes
WHERE index_name = k_object_name;
DBMS_OUTPUT.PUT_LINE(CHR(10)||'Table ' || v_table_name);
END IF;
OPEN :output FOR
SELECT i.index_name
, DECODE(i.uniqueness,
'UNIQUE', 'Y', 'N') AS uniqueness
, i.index_type
, i.status
, i.num_rows
, i.distinct_keys
, c.column_name
FROM user_indexes i
, user_ind_columns c
WHERE i.table_name = v_table_name
AND ( ( v_object_type = 'INDEX' AND i.index_name = k_object_name )
OR v_object_type = 'TABLE' )
AND c.index_name = i.index_name
AND c.table_name = i.table_name
ORDER BY i.index_name, c.column_position;
EXCEPTION
WHEN NO_DATA_FOUND THEN
OPEN :output FOR
SELECT 'No such table or index "' || k_object_name || '"' AS error
FROM dual;
END;
/
@sqlplus_settings.sql
set term on</pre>
For a constraints report you could have a look at constr.sql (requires you to create a couple of types).
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
