V_QUERY_QUERYVIEW
Provides support for querying on Query View metadata from the application catalog.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| NAME | nvarchar(255) | Name | |
| DESCRIPTION | nvarchar(max) | Description | |
| AUTHOR | nvarchar(500) | yes | Author |
| RECORDTYPENAME | nvarchar(50) | yes | Record type |
| OBJECTTYPENAME | varchar(20) | Object type name | |
| OBJECTNAME | nvarchar(128) | Object name | |
| IMPLEMENTATIONDESCRIPTION | nvarchar(151) | Implementation description | |
| ROOTOBJECT | bit | Root | |
| ENFORCERECORDACCESSSECURITY | bit | Enforce record access security | |
| USEINREPORTMODELGENERATOR | bit | Use in report model generator | |
| PRIMARYKEYFIELD | nvarchar(128) | yes | Primary key field |
| PRIMARYKEYTYPENAME | nvarchar(128) | yes | Primary key type name |
| SUBGROUP | nvarchar(255) | Subgroup | |
| ISBUILTIN | int | yes | Is built in |
| SECURITYUIDISPLAYFEATURE | int | yes | Security UI display feature |
| SECURITYUIFOLDER | nvarchar(255) | Security UI folder | |
| INSTALLEDPRODUCTSLIST | nvarchar(max) | yes | Installed products list |
| INSTALLED | bit | yes | Installed |
| EXTENSIBLE | int | Extensible | |
| QUERYVIEWSPECXML | xml | yes | Query view spec xml |
| RESOURCEFILE | nvarchar(max) | yes | Resource file |
| VIEWSQL | nvarchar(max) | yes | View sql |
| CREATEFUNCTIONSQL | nvarchar(max) | yes | Create function sql |
| ID | uniqueidentifier | System record ID | |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| USEFOREXPORTDEFINITIONSONLY | int | yes | |
| MARTKEY | nvarchar(max) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:30:56 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_QUERYVIEW AS
with xmlnamespaces ('bb_appfx_queryview' as tns, 'bb_appfx_commontypes' as common)
select
Q.DISPLAYNAME as NAME,
Q.DESCRIPTION,
Q.QUERYVIEWSPEC.value('(tns:QueryViewSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
RT.NAME as RECORDTYPENAME,
Q.OBJECTTYPENAME,
Q.OBJECTNAME,
Q.OBJECTTYPENAME + ' (' + Q.OBJECTNAME + ')' as IMPLEMENTATIONDESCRIPTION,
Q.ROOTOBJECT,
Q.ENFORCERECORDACCESSSECURITY,
Q.USEINREPORTMODELGENERATOR,
Q.PRIMARYKEYFIELD,
Q.PRIMARYKEYTYPENAME,
Q.SUBGROUP,
coalesce(Q.QUERYVIEWSPEC.value('(tns:QueryViewSpec/@IsBuiltIn)[1]', 'bit'), 0) as ISBUILTIN,
coalesce(Q.QUERYVIEWSPEC.value('(tns:QueryViewSpec/@SecurityUIDisplayFeature)[1]', 'bit'), 1) as SECURITYUIDISPLAYFEATURE,
Q.SECURITYUIFOLDER,
dbo.UFN_INSTALLEDPRODUCTS_TRANSLATELIST(Q.QUERYVIEWSPEC.query('tns:QueryViewSpec/common:InstalledProductList')) as INSTALLEDPRODUCTSLIST,
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(Q.QUERYVIEWSPEC.query('tns:QueryViewSpec/common:InstalledProductList')) as INSTALLED,
case
when (Q.OBJECTTYPE = 0) and (cast(Q.QUERYVIEWSPEC as nvarchar(max)) like '%/*#EXTENSION*/%') then 1
else 0
end as EXTENSIBLE,
Q.QUERYVIEWSPEC as QUERYVIEWSPECXML,
case Q.QUERYVIEWSPEC.exist('tns:QueryViewSpec/tns:ResourceFile')
when 1 then
Q.QUERYVIEWSPEC.value('(tns:QueryViewSpec/tns:ResourceFile/@AssemblyName)[1]', 'nvarchar(max)') + ', ' +
Q.QUERYVIEWSPEC.value('(tns:QueryViewSpec/tns:ResourceFile/@ClassName)[1]', 'nvarchar(max)')
else null
end as RESOURCEFILE,
case Q.QUERYVIEWSPEC.exist('tns:QueryViewSpec/tns:ViewImplementation/tns:ViewSQL')
when 1 then Q.QUERYVIEWSPEC.value('(tns:QueryViewSpec/tns:ViewImplementation/tns:ViewSQL)[1]', 'nvarchar(max)')
else null
end as VIEWSQL,
case Q.QUERYVIEWSPEC.exist('tns:QueryViewSpec/tns:TVFImplementation/tns:CreateFunctionSQL')
when 1 then Q.QUERYVIEWSPEC.value('(tns:QueryViewSpec/tns:TVFImplementation/tns:CreateFunctionSQL)[1]', 'nvarchar(max)')
else null
end as CREATEFUNCTIONSQL,
Q.ID,
ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
Q.DATEADDED,
Q.DATECHANGED,
Q.TSLONG,
coalesce(Q.QUERYVIEWSPEC.value('(tns:QueryViewSpec/@UseForExportDefinitionsOnly)[1]', 'bit'), 0) as USEFOREXPORTDEFINITIONSONLY,
Q.QUERYVIEWSPEC.value('(tns:QueryViewSpec/@MartKey)[1]', 'nvarchar(max)') as MARTKEY
/*#EXTENSION*/
from dbo.QUERYVIEWCATALOG as Q
left outer join dbo.RECORDTYPE as RT on Q.RECORDTYPEID = RT.ID
left outer join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = Q.ADDEDBYID
left outer join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = Q.CHANGEDBYID