Dynamic SQL to recreate existing USI

Database
Enthusiast

Dynamic SQL to recreate existing USI

I am in the process of scrubbing some timestamp fields in our prod environment.  What I want to do is (1) drop the USI, (2) scrub the data -- timestamp part of the usi, (3) re-create the usi.  I am able to dynamically create the 'drop usi' script but have not been able to figure out how to dynamically recreate a USI.  Anyone have a handy dandy script for me out there?

1 REPLY
Enthusiast

Re: Dynamic SQL to recreate existing USI

Here is a some recursive SQL that will get you down the path to accomplishing what you need to do. It will need some tweaking to drop out any columns that whose data type is TIMESTAMP.

WITH RECURSIVE cte (DatabaseName, TableName, IndexName, IndexDDL, LEVEL) AS
(SELECT i.DatabaseName
, i.TableName
, i.IndexName
, 'CREATE INDEX ' || TRIM(i.IndexName) || ' ON ' || TRIM(i.DatabaseName)
|| '.' || TRIM(i.TableName) || '( ' || TRIM(i.ColumnName)
, 1
FROM dbc.indices i
, dbc.COLUMNS c
WHERE i.databasename = c.databasename
AND i.tablename = c.tablename
AND i.columnname = c.columnname
AND i.indextype = 's' -- Secondary Index
AND i.databasename = '?databasename'
AND i.tablename = '?tablename'
AND i.columnposition = 1

UNION ALL

SELECT i.DatabaseName
, i.TableName
, i.IndexName
, cte.IndexDDL || ', ' || TRIM(i.ColumnName)
, cte.LEVEL + 1
FROM dbc.indices i
, dbc.COLUMNS c
, cte
WHERE i.databasename = c.databasename
AND i.tablename = c.tablename
AND i.columnname = c.columnname
AND i.indextype = 's' -- Secondary Index
AND i.databasename = '?databasename'
AND i.tablename = '?tablename'
AND i.columnposition = cte.LEVEL + 1
AND cte.LEVEL < 32
)
SELECT LEVEL
, DatabaseName
, TableName
, IndexDDL || ');' AS IndexDDL_
FROM cte
QUALIFY MAX(LEVEL) OVER(PARTITION BY DatabaseName, TableName) = LEVEL
ORDER BY LEVEL;