| Subject | How to identify if field of table is identity or not? |
|---|---|
| Author | hamacker |
| Post date | 2017-01-12T16:22:05Z |
hi All,
I did a procedure that recompute all identity fields of a list with items separate by comma, but I need a method to check if table.field is identity or not. It´s a procedure of mainnitence.
My code:
EXECUTE BLOCK
returns (
result_table varchar(255),
result_idkey varchar(255),
result_done varchar(1)) as
declare variable source_list varchar(8192);
declare variable l_item varchar(255);
declare variable l_lastpos integer;
declare variable l_nextpos integer;
declare variable l_npos integer;
declare variable l_idkey_max bigint = 0;
declare variable l_is_identity integer;
begin
source_list='cv.id_cv,clientes.id_cliente,fornecedores.id_fornecedor';
source_list = :source_list || ',';
l_lastpos = 1;
l_nextpos = position(',', :source_list, l_lastpos);
while (:l_nextpos > 1) do
begin
l_item = substring(:source_list from :l_lastpos for :l_nextpos - :l_lastpos);
result_done='N';
if (trim(l_item)<>'') then
begin
l_npos = position('.', l_item);
if (l_npos>0) then
begin
result_table = substring(:l_item from 1 for l_npos - 1);
result_idkey = substring(:l_item from l_npos +1);
-- se a tabela existir, então captura a numeracao mais alta e a transfere para o sequenciador
if (exists(SELECT * FROM RDB$RELATIONS WHERE UPPER(RDB$RELATION_NAME) = UPPER(:result_table))) then
begin
execute statement 'select max('||:result_idkey||') FROM '||:result_table into :l_idkey_max;
if (:l_idkey_max is null)
then l_idkey_max=0;
l_idkey_max=:l_idkey_max+1;
l_is_identity=1;
-- todo: How to identify if field of table is identity or not?
if (l_is_identity>0) then
begin
execute statement 'ALTER TABLE '||:result_table||' ALTER COLUMN '||:result_idkey|| ' RESTART WITH '||CAST(:l_idkey_max as VARCHAR(8))||';';
result_done = 'Y';
end
suspend;
end
end
end
l_lastpos = :l_nextpos + 1;
l_nextpos = position(',', :source_list, l_lastpos);
end
end