V_QUERY_DATALIST
Provides support for querying on Data List metadata from the application catalog.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| NAME | nvarchar(60) | Name | |
| DESCRIPTION | nvarchar(1000) | Description | |
| AUTHOR | nvarchar(500) | yes | Author |
| RECORDTYPENAME | nvarchar(50) | yes | Record type |
| IMPLEMENTATIONTYPENAME | varchar(3) | Implementation type | |
| IMPLEMENTATIONDESCRIPTION | nvarchar(264) | yes | Implementation description |
| CONTEXTPARAMETERNAME | nvarchar(128) | Context parameter name | |
| SECURITYUIFOLDER | nvarchar(255) | Security UI folder | |
| SECURITYUIDISPLAYFEATURE | int | yes | Security UI display feature |
| BPSECURITYCONTEXTIDISPARAMETERSETID | int | yes | Context ID is parameter set ID |
| SKIPOUTPUTSCHEMAVALIDATION | int | yes | Skip output schema validation |
| INSTALLEDPRODUCTSLIST | nvarchar(max) | yes | Installed products list |
| INSTALLED | bit | yes | Installed |
| DEFAULTIMAGEKEY | nvarchar(max) | yes | Default image key |
| DATALISTSPECXML | xml | Data list spec xml | |
| RESOURCEFILE | nvarchar(max) | yes | Resource file |
| PROCEDURENAME | nvarchar(128) | Procedure name | |
| CREATEPROCEDURESQL | nvarchar(max) | yes | Create procedure sql |
| ASSEMBLYNAME | nvarchar(128) | Assembly name | |
| CLASSNAME | nvarchar(128) | Class name | |
| RSSFEEDSTYLE | nvarchar(4) | yes | Feed style |
| CHANNELTITLE | nvarchar(max) | yes | Channel title |
| CHANNELDESCRIPTION | nvarchar(max) | yes | Channel description |
| CHANNELAUTHOR | nvarchar(max) | yes | Channel author |
| DATAWINDOWOPENPARAMETER | nvarchar(200) | yes | DataWindowOpenParameter |
| DATAWINDOWCLOSEPARAMETER | nvarchar(200) | yes | DataWindowCloseParameter |
| SITESECURITYTABLENAME | nvarchar(200) | yes | Table name |
| SITESECURITYCOLUMNNAME | nvarchar(200) | yes | Column name |
| FORMUICOMPONENTTYPE | varchar(16) | Form UI component type | |
| FORMCOMPONENTASSEMBLYNAME | nvarchar(max) | yes | Form component assembly name |
| FORMCOMPONENTCLASSNAME | nvarchar(max) | yes | Form component class name |
| WEBUICOMPONENTTYPE | varchar(17) | Web UI component type | |
| WEBUIMODELASSEMBLYNAME | nvarchar(max) | yes | Web UI model assembly name |
| WEBUIMODELCLASSNAME | nvarchar(max) | yes | Web UI model class name |
| WEBUIEXTERNALRESOURCEURL | nvarchar(max) | yes | Web UI external resource url |
| 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 |
| SPECUINAME | nvarchar(60) | Name override | |
| UINAME | nvarchar(60) | Display name | |
| NOSECURITYREQUIRED | int | yes | |
| MARTKEY | nvarchar(max) | yes | |
| LASTUPDATEDSOURCE | varchar(16) | yes | |
| LASTUPDATEDSOURCEOLAPDATASOURCEID | uniqueidentifier | yes | |
| LASTUPDATEDSOURCEFORMFIELDID | nvarchar(128) | 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_DATALIST AS
with xmlnamespaces ('bb_appfx_datalist' as tns, 'bb_appfx_commontypes' as common)
select
D.NAME,
D.DESCRIPTION,
D.DATALISTSPEC.value('(tns:DataListSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
RT.NAME as RECORDTYPENAME,
D.IMPLEMENTATIONTYPENAME,
case D.IMPLEMENTATIONTYPE
when 0 then 'SP (' + D.PROCEDURENAME + ')'
when 1 then 'CLR (' + D.ASSEMBLYNAME + ', ' + D.CLASSNAME + ')'
end as IMPLEMENTATIONDESCRIPTION,
D.CONTEXTPARAMETERNAME,
D.SECURITYUIFOLDER,
coalesce(D.DATALISTSPEC.value('(tns:DataListSpec/@SecurityUIDisplayFeature)[1]', 'bit'), 1) as SECURITYUIDISPLAYFEATURE,
coalesce(D.DATALISTSPEC.value('(tns:DataListSpec/@BPSecurityContextIDIsParameterSetID)[1]', 'bit'), 0) as BPSECURITYCONTEXTIDISPARAMETERSETID,
coalesce(D.DATALISTSPEC.value('(tns:DataListSpec/@SkipOutputSchemaValidation)[1]', 'bit'), 0) as SKIPOUTPUTSCHEMAVALIDATION,
dbo.UFN_INSTALLEDPRODUCTS_TRANSLATELIST(D.DATALISTSPEC.query('tns:DataListSpec/common:InstalledProductList')) as INSTALLEDPRODUCTSLIST,
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(D.DATALISTSPEC.query('tns:DataListSpec/common:InstalledProductList')) as INSTALLED,
D.DATALISTSPEC.value('(tns:DataListSpec/tns:Output/@DefaultImageKey)[1]', 'nvarchar(max)') as DEFAULTIMAGEKEY,
D.DATALISTSPEC as DATALISTSPECXML,
case D.DATALISTSPEC.exist('tns:DataListSpec/tns:ResourceFile')
when 1 then
D.DATALISTSPEC.value('(tns:DataListSpec/tns:ResourceFile/@AssemblyName)[1]', 'nvarchar(max)') + ', ' +
D.DATALISTSPEC.value('(tns:DataListSpec/tns:ResourceFile/@ClassName)[1]', 'nvarchar(max)')
else null
end as RESOURCEFILE,
D.PROCEDURENAME,
case D.DATALISTSPEC.exist('tns:DataListSpec/tns:SPDataList/common:CreateProcedureSQL')
when 1 then D.DATALISTSPEC.value('(tns:DataListSpec/tns:SPDataList/common:CreateProcedureSQL)[1]', 'nvarchar(max)')
else null
end as CREATEPROCEDURESQL,
D.ASSEMBLYNAME,
D.CLASSNAME,
case D.DATALISTSPEC.exist('tns:DataListSpec/tns:RSSFeed')
when 1 then coalesce(D.DATALISTSPEC.value('(tns:DataListSpec/tns:RSSFeed/@style)[1]', 'nvarchar(4)'), 'feed')
else null
end as RSSFEEDSTYLE,
case D.DATALISTSPEC.exist('tns:DataListSpec/tns:RSSFeed')
when 1 then D.DATALISTSPEC.value('(tns:DataListSpec/tns:RSSFeed/tns:Channel/tns:Title/@Caption)[1]', 'nvarchar(max)')
else null
end as CHANNELTITLE,
case D.DATALISTSPEC.exist('tns:DataListSpec/tns:RSSFeed')
when 1 then D.DATALISTSPEC.value('(tns:DataListSpec/tns:RSSFeed/tns:Channel/tns:Description/@Caption)[1]', 'nvarchar(max)')
else null
end as CHANNELDESCRIPTION,
case D.DATALISTSPEC.exist('tns:DataListSpec/tns:RSSFeed')
when 1 then D.DATALISTSPEC.value('(tns:DataListSpec/tns:RSSFeed/tns:Channel/tns:Author/@Caption)[1]', 'nvarchar(max)')
else null
end as CHANNELAUTHOR,
case D.DATALISTSPEC.exist('tns:DataListSpec/tns:EmailFeedAlert')
when 1 then D.DATALISTSPEC.value('(tns:DataListSpec/tns:EmailFeedAlert/@DataWindowOpenParameter)[1]', 'nvarchar(200)')
else null
end as DATAWINDOWOPENPARAMETER,
case D.DATALISTSPEC.exist('tns:DataListSpec/tns:EmailFeedAlert')
when 1 then D.DATALISTSPEC.value('(tns:DataListSpec/tns:EmailFeedAlert/@DataWindowCloseParameter)[1]', 'nvarchar(200)')
else null
end as DATAWINDOWCLOSEPARAMETER,
case D.DATALISTSPEC.exist('tns:DataListSpec/tns:SiteSecurity')
when 1 then D.DATALISTSPEC.value('(tns:DataListSpec/tns:SiteSecurity/@TableName)[1]', 'nvarchar(200)')
else null
end as SITESECURITYTABLENAME,
case D.DATALISTSPEC.exist('tns:DataListSpec/tns:SiteSecurity')
when 1 then D.DATALISTSPEC.value('(tns:DataListSpec/tns:SiteSecurity/@ColumnName)[1]', 'nvarchar(200)')
else null
end as SITESECURITYCOLUMNNAME,
case D.DATALISTSPEC.exist('tns:DataListSpec/tns:Parameters/common:FormMetaData/common:FormUIComponent')
when 1 then
case D.DATALISTSPEC.value('(tns:DataListSpec/tns:Parameters/common:FormMetaData/common:FormUIComponent/@FormUIComponentType)[1]', 'nvarchar(15)')
when 'CustomComponent' then 'Custom component'
else 'Default UI'
end
else
'None'
end as FORMUICOMPONENTTYPE,
D.DATALISTSPEC.value('(tns:DataListSpec/tns:Parameters/common:FormMetaData/common:FormUIComponent/common:CustomComponentID/@AssemblyName)[1]', 'nvarchar(max)') as FORMCOMPONENTASSEMBLYNAME,
D.DATALISTSPEC.value('(tns:DataListSpec/tns:Parameters/common:FormMetaData/common:FormUIComponent/common:CustomComponentID/@ClassName)[1]', 'nvarchar(max)') as FORMCOMPONENTCLASSNAME,
case D.DATALISTSPEC.exist('tns:DataListSpec/tns:Parameters/common:FormMetaData/common:WebUIComponent')
when 1 then
case D.DATALISTSPEC.exist('tns:DataListSpec/tns:Parameters/common:FormMetaData/common:WebUIComponent/common:WebUI/common:ExternalResource')
when 1 then 'External resource'
else 'Default UI'
end
else
'None'
end as WEBUICOMPONENTTYPE,
D.DATALISTSPEC.value('(tns:DataListSpec/tns:Parameters/common:FormMetaData/common:WebUIComponent/common:UIModel/@AssemblyName)[1]', 'nvarchar(max)') as WEBUIMODELASSEMBLYNAME,
D.DATALISTSPEC.value('(tns:DataListSpec/tns:Parameters/common:FormMetaData/common:WebUIComponent/common:UIModel/@ClassName)[1]', 'nvarchar(max)') as WEBUIMODELCLASSNAME,
D.DATALISTSPEC.value('(tns:DataListSpec/tns:Parameters/common:FormMetaData/common:WebUIComponent/common:WebUI/common:ExternalResource/@Url)[1]', 'nvarchar(max)') as WEBUIEXTERNALRESOURCEURL,
D.ID,
ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
D.DATEADDED,
D.DATECHANGED,
D.TSLONG,
D.SPECUINAME,
D.UINAME,
coalesce(D.DATALISTSPEC.value('(tns:DataListSpec/@NoSecurityRequired)[1]', 'bit'), 0) as NOSECURITYREQUIRED,
D.DATALISTSPEC.value('(tns:DataListSpec/@MartKey)[1]', 'nvarchar(max)') as MARTKEY,
case D.DATALISTSPEC.exist('tns:DataListSpec/tns:LastUpdatedSource/common:OlapDataSource')
when 1 then 'OLAP data source'
else case D.DATALISTSPEC.exist('tns:DataListSpec/tns:LastUpdatedSource/common:FormField')
when 1 then 'Form field'
else null
end
end as LASTUPDATEDSOURCE,
case D.DATALISTSPEC.exist('tns:DataListSpec/tns:LastUpdatedSource/common:OlapDataSource')
when 1 then D.DATALISTSPEC.value('(tns:DataListSpec/tns:LastUpdatedSource/common:OlapDataSource/@ID)[1]', 'uniqueidentifier')
else null
end as LASTUPDATEDSOURCEOLAPDATASOURCEID,
case D.DATALISTSPEC.exist('tns:DataListSpec/tns:LastUpdatedSource/common:FormField')
when 1 then D.DATALISTSPEC.value('(tns:DataListSpec/tns:LastUpdatedSource/common:FormField/@FieldID)[1]', 'nvarchar(128)')
else null
end as LASTUPDATEDSOURCEFORMFIELDID
/*#EXTENSION*/
from dbo.DATALISTCATALOG as D
left outer join dbo.RECORDTYPE as RT on D.RECORDTYPEID = RT.ID
left outer join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = D.ADDEDBYID
left outer join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = D.CHANGEDBYID