如何在SAP HANA中生成删除与重建约束、索引、触发器的脚本？

在批量更新数据库所有表前，需先移除表中的索引、触发器和约束，完成更新后再重建这些对象。以下是生成对应删除与重建SQL语句的脚本：

1. 外键约束的删除与重建脚本 #

该脚本会生成所有外键约束的删除语句，以及对应的重建语句：

select 
    'alter table '|| quote_ident(ns.nspname)||'.'||quote_ident(tb.relname)||' drop constraint '||quote_ident(conname)||';' as 删除语句,
    'alter table '|| quote_ident(ns.nspname)||'.'||quote_ident(tb.relname)||' add constraint '||quote_ident(conname)||' '||pg_get_constraintdef(c.oid, true)||';' as 重建语句,
    'CONSTRAINT' 对象类型,
    quote_ident(ns.nspname) as 模式名,
    quote_ident(tb.relname) as 表名,
    quote_ident(conname) as 约束名,
    ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY quote_ident(ns.nspname), quote_ident(tb.relname)) 行号
from pg_constraint c 
join pg_class tb on tb.oid = c.conrelid
join pg_namespace ns on ns.oid = tb.relnamespace 
where 1 = 1
-- 可注释掉上面的1=1，放开下面的行指定特定模式，比如只处理public模式
-- and ns.nspname in ('public')
and c.contype = 'f' -- 'f'代表外键约束

2. 触发器的禁用与启用脚本 #

由于删除触发器后重建需要完整的触发器定义，更安全的方式是先禁用触发器，更新完成后再启用：

select 
    'alter table "' || event_object_schema || '"."' || event_object_table || '" disable trigger "' || trigger_name || '";' AS 删除语句,
    'alter table "' || event_object_schema || '"."' || event_object_table || '" enable trigger "' || trigger_name || '";' AS 重建语句,
    'TRIGGER' 对象类型,
    event_object_schema as 模式名,
    event_object_table as 表名,
    trigger_name as 触发器名,
    ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY event_object_schema, event_object_table) 行号
from information_schema.triggers

3. 索引的删除与重建脚本 #

补充生成所有用户自定义索引的删除与重建语句：

select
    'drop index ' || quote_ident(ns.nspname) || '.' || quote_ident(idx.relname) || ';' as 删除语句,
    'create ' || case when idx.indisunique then 'unique ' else '' end || 'index ' || quote_ident(idx.relname) || ' on ' || quote_ident(ns.nspname) || '.' || quote_ident(tb.relname) || ' using ' || am.amname || ' (' || array_to_string(array_agg(quote_ident(att.attname) || case when idxkey.indisdesc then ' desc' else '' end), ', ') || ');' as 重建语句,
    'INDEX' 对象类型,
    quote_ident(ns.nspname) as 模式名,
    quote_ident(tb.relname) as 表名,
    quote_ident(idx.relname) as 索引名,
    ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY quote_ident(ns.nspname), quote_ident(tb.relname)) 行号
from pg_index idx
join pg_class tb on idx.indrelid = tb.oid
join pg_class idx_rel on idx.indexrelid = idx_rel.oid
join pg_namespace ns on tb.relnamespace = ns.oid
join pg_am am on idx_rel.relam = am.oid
join pg_attribute att on tb.oid = att.attrelid and att.attnum = any(idx.indkey)
join pg_index idxkey on idx.indexrelid = idxkey.indexrelid
where idx.indisprimary = false -- 排除主键索引（主键属于约束，已在第一部分处理）
  and idx.indisunique = false -- 排除唯一约束索引（也属于约束）
group by ns.nspname, tb.relname, idx_rel.relname, idx.indisunique, am.amname

内容的提问来源于stack exchange，提问作者Umid Umaraliev