Here is a Denodo Virtual Query Language (VQL) code snippet to add a primary key to multiple derived views in Virtual DataPort (VDP). This code snippet assumes the primary key is the first field in the table. Useful when needing to do bulk work on data warehouse-style views where the surrogate key of the view is the first field. I have found the script occasionally helpful when working with a large number of views, which need to be updated.
VQL To Add A Primary Key On A Semantic Views
select ‘ALTER VIEW ‘|| ‘ “‘||View_name||'” ‘|| ‘ADD CONSTRAINT ‘ ||”’PK_’||Column_Name||”’ ‘||’ PRIMARY KEY (”’|| Column_Name || ”’ ) ;’
From(Select AA.view_name,AA.Column_name
from
(
Select View_name, Column_Name
FROM GET_VIEW_COLUMNS()
WHERE input_database_name=’<<Database_Name>>‘
and Ordinal_Position = 1
) AA,
(SELECT Name View_name , folder
FROM GET_VIEWS()
WHERE input_database_name = ‘<<Database_Name>>‘
AND folder = ‘<<Folder_Path>>‘) AB
where AA.View_name = AB. View_name
)BA