GET_ELEMENTS¶
Description
The stored procedure GET_ELEMENTS
returns information about any element: data sources, views, Web services, etc.
You can filter the result by several parameters: element name, type, etc.
Each row represents an element.
If you only want to obtain information about views, it is better if you use the procedure GET_VIEWS because it provides more information than this one.
Syntax
GET_ELEMENTS (
input_database_name : text
, input_name : text
, input_type : element type
, input_user_creator : text
, input_last_user_modifier : text
, input_init_create_date : date
, input_end_create_date : date
, input_init_last_modification_date : date
, input_end_last_modification_date : date
, input_description : text
)
<element type> ::=
NULL
| 'Folders'
| 'DataSources'
| 'StoredProcedures'
| 'Wrappers'
| 'Views'
| 'WebServices'
| 'Widgets'
| 'Associations'
| 'JMSListeners'
| 'GlobalSecurityPolicies'
| 'Tags'
If you invoke the procedure using
CALL
and do not want to filter by a parameter, passnull
.The procedure evaluates the input parameters with AND conditions. E.g. if you pass a value to
input_database_name
,input_name
andinput_description
, the procedure will search return elements of this database and this name and that contains this description.The procedure evaluates the following parameters with the operator LIKE instead of equals:
input_name
input_user_creator
input_last_user_modifier
input_description
This means that in the value of these parameters, you can use the wildcard operators you use with LIKE (
%
and_
). The search by the description is case insensitive.When providing a value for
input_init_create_date
andinput_end_create_date
, orinput_init_last_modification_date
andinput_end_last_modification_date
, the procedure returns the elements between the two intervals. I.e.: when you provide a value forinput_init_create_date
andinput_end_create_date
, the procedure returns the elements created during these two dates.If
input_init_create_date
isnull
, the procedure returns all the elements that were created beforeinput_end_create_date
.If
input_end_create_date
isnull
, the procedure returns all the elements that were created afterinput_init_create_date
. Searching byinput_init_last_modification_date
andinput_end_last_modification_date
works in the same way.
The procedure returns these fields:
database_name
: name of database where the element belongs to.name
: name of the element.type
: type of the element. The values can beassociation
datasource
folder
storedProcedure
type
view
webService
widget
wrapper
subtype
: subtype of the element or an empty string if the element does not have a subtype. Elements that have a subtype and what subtypes they can have:view
:base
,derived
,interface
ormaterialized
datasource
:custom
,df
,essbase
,jdbc
,json
,ldap
,odbc
,olap
,salesforce
,sapbwbapi
,saperp
,ws
orxml
wrapper
:custom
,df
,essbase
,html
,jdbc
,json
,ldap
,odbc
,olap
,salesforce
,sapbwbapi
,saperp
,ws
orxml
storedProcedure
:user defined
,user defined - vql
user_creator
: owner of the element.last_user_modifier
: user that modified the element for the last time. If the element was never modified, the value is the same asuser_creator
.create_date
: date when the element was created.last_modification_date
: date when the element was modified for the last time. If the element was never modified, the value is the same ascreate_date
description
: description of the element.folder
: folder of the element in lowercase. If the element is not in any folder, the value is/
.base_view_type
: the wrapper subtype of a base view ornull
if the element it is not a base view. The subtypes that they can have are:custom
,df
,essbase
,html
,jdbc
,json
,ldap
,odbc
,olap
,salesforce
,sapbwbapi
,saperp
,ws
orxml
.
Privileges Required
The results of this procedure change depending on the privileges granted to the user that runs it. If the user is not an administrator user, consider the following:
If the parameter
input_database_name
is notnull
, the procedure returns an error if the user does not have CONNECT privileges over this database.The procedure will only return information about the elements over which the user has
METADATA
privileges.
Examples
Example 1
SELECT *
FROM GET_ELEMENTS()
WHERE input_database_name = 'customer_report' AND folder = '/base views'
Obtains all the elements of the database “customer_report” inside a particular folder. Note that folder
is not an input parameter of the procedure. Therefore, the execution engine executes the procedure passing the parameter input_database_name
. The result is the information about all the elements in that database. Then, the execution engine filters this result to return only the folders whose name is “base views”.
Example 2
SELECT *
FROM GET_ELEMENTS()
WHERE
input_database_name = 'customer_report'
AND (input_init_create_date = ADDDAY(CURRENT_DATE, -1));
Obtains all the elements of the database “customer_report” created since yesterday at 12 AM.
Example 3
SELECT *
FROM GET_ELEMENTS()
WHERE input_type = 'views' AND input_description = '%report%';
This query returns all the views whose description contains the word “report”.