|
SQL Stored Procedure - Index Listing Utility
CREATE PROCEDURE qgpl.indexlist(TableName VARCHAR(128), SchemaName VARCHAR(128) )
LANGUAGE SQL
RESULT SET 1
BEGIN
DECLARE ixTable VARCHAR(128);
DECLARE ixTableSchema VARCHAR(128);
DECLARE c1 CURSOR WITH RETURN FOR
WITH SQL_Indices
(index_name, index_schema, is_unique, system_index_name, system_index_schema)
AS ( SELECT index_name, index_schema, is_unique, system_index_name,
system_index_schema
FROM qsys2.sysindexes
WHERE table_name = ixTable AND table_schema = ixTableSchema ),
Const_Indices (index_name, index_schema, is_unique)
AS ( SELECT constraint_name, constraint_schema,
CASE WHEN constraint_type = 'FOREIGN KEY' THEN 'D' ELSE 'U' END
FROM qsys2.syscst
WHERE table_name = ixTable AND table_schema = ixTableSchema
and constraint_type<>'CHECK'),
KeyedLF_Indices (index_name, index_schema, is_unique)
AS ( SELECT dbxfil, dbxlib, dbxunq
FROM qsys.qadbxref, qsys.qadbfdep
WHERE dbxatr='LF' AND dbxnkf>0 AND dbffdp=dbxfil AND dbfldp=dbxlib
AND dbffil=ixTable
AND dbflib=ixTableSchema),
KeyedPF_Index (index_name, index_schema, is_unique,dbxfil)
AS (SELECT dbxlfi,dbxlib,dbxunq,dbxfil
FROM qsys.qadbxref
WHERE dbxatr='PF' AND dbxnkf>0 AND dbxlib=ixTableSchema AND dbxlfi=ixTable
AND NOT EXISTS ( SELECT 1 FROM qsys.qadbfcst
WHERE dbxlib=dbccfl AND dbxfil=dbccff AND dbccty='PRIMARY KEY') )
/* -----------------------------------------------------------------------------*/
/* SQL Indices */
/*------------------------------------------------------------------------------*/
SELECT
CASE
WHEN ordinal_position = 1
THEN SUBSTRING(SQL_Indices.index_name, 1, 30)
ELSE ' '
END as INDEX_NAME,
CASE
WHEN ordinal_position = 1
THEN SUBSTRING(SQL_Indices.index_schema, 1, 10)
ELSE ' '
END as INDEX_SCHEMA,
is_unique,
SUBSTRING(column_name, 1, 30) as KEY_COLUMN,
ordinal_position,
ordering ,
SQL_Indices.system_index_name,
SQL_Indices.system_index_schema
FROM SQL_Indices, qsys2.syskeys B
WHERE SQL_Indices.system_index_name = B.system_index_name
AND SQL_Indices.system_index_schema = B.system_index_schema
UNION
/* -----------------------------------------------------------------------------*/
/* Constraint Indices */
/*------------------------------------------------------------------------------*/
SELECT
CASE
WHEN ordinal_position = 1
THEN SUBSTRING(Const_Indices.index_name, 1, 30)
WHEN ordinal_position > 1 THEN ' '
END,
CASE
WHEN ordinal_position = 1
THEN SUBSTRING(Const_Indices.index_schema, 1, 10)
WHEN ordinal_position > 1 THEN ' '
END,
is_unique,
SUBSTRING(column_name, 1, 30),
ordinal_position,
'A' ,
index_name,
index_schema
FROM Const_Indices, qsys2.syskeycst B
WHERE Const_Indices.index_name = B.constraint_name
AND Const_Indices.index_schema = B.constraint_schema
UNION
/* -----------------------------------------------------------------------------*/
/* Keyed LF Indices */
/*------------------------------------------------------------------------------*/
SELECT
CASE
WHEN dbkpos = 1
THEN SUBSTRING(KeyedLF_Indices.index_name, 1, 10)
ELSE ' '
END,
CASE
WHEN dbkpos = 1
THEN SUBSTRING(KeyedLF_Indices.index_schema, 1, 10)
ELSE ' '
END,
is_unique,
dbkfld,
dbkpos,
dbkord,
index_name,
index_schema
FROM KeyedLF_Indices, qsys.qadbkfld B
WHERE KeyedLF_Indices.index_name = dbkfil
AND KeyedLF_Indices.index_schema = dbklib
UNION
/* -----------------------------------------------------------------------------*/
/* Keyed PF Indices */
/*------------------------------------------------------------------------------*/
SELECT
CASE
WHEN dbkpos = 1
THEN SUBSTRING(KeyedPF_Index.index_name, 1, 10)
ELSE ' '
END,
CASE
WHEN dbkpos = 1
THEN SUBSTRING(KeyedPF_Index.index_schema, 1, 10)
ELSE ' '
END,
is_unique,
dbkfld,
dbkpos,
dbkord,
index_name,
index_schema
FROM KeyedPF_Index, qsys.qadbkfld B
WHERE KeyedPF_Index.index_name = dbkfil
AND KeyedPF_Index.index_schema = dbklib
ORDER BY 7,8,5 FOR READ ONLY;
SELECT table_name, table_schema INTO ixTable, ixTableSchema
FROM qsys2.systables
WHERE (table_name=UPPER(TableName) and table_schema=UPPER(SchemaName)) OR
( system_table_name=UPPER(TableName)
and system_table_schema=UPPER(SchemaName));
/* Open the cursor so the index information can be returned as a result set */
OPEN c1;
END
| |
|
