Physical Modeling › Scripts › Script Use Examples

Script Use Examples

The following examples illustrate some of the uses of scripts:

Grant user permissions at the schema level

%Forgettable()
{
grant select on %TableName to %TableProp(GrantSelect)
%DBMSDelim
}

Grant user permissions at the table level

grant select %TableName to %TableProp(GrantSelect)
%DBMSDelim

Drop tables based upon owner information (provided in the form of a UDP)

%ForEachTable()
{
if exists (drop table %TableProp(TableOwner) .%TableName)
%DBMSDelim
}

Drop table constraints

%ForEachTable()
{
    %ForEachIndex()
    { 
     if exists (drop table %TableProp(TableOwner) .%TableName)
     %DBMSDelim
    }
}

Create generic database view with * (asterisk)

%ForEachTable()
{
    create view v_%TableName as
      (select * from %TableName)
%DBMSDelim
}

Create a database view with column names

%ForEachTable()
{
create view v_%TableName as
(
select %ForEachColumn(,","){%ColName}
from %TableName
)
%DBMSDelim
}