V_QUERY_SIMPLEDATALIST
Provides support for querying on Simple 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 |
| IMPLEMENTATIONTYPENAME | varchar(3) | Implementation type | |
| IMPLEMENTATIONDESCRIPTION | nvarchar(264) | yes | Implementation description |
| INSTALLEDPRODUCTSLIST | nvarchar(max) | yes | Installed products list |
| INSTALLED | bit | yes | Installed |
| DEFAULTIMAGEKEY | nvarchar(max) | yes | Default image key |
| NOSECURITYREQUIRED | int | yes | No security required |
| SIMPLEDATALISTSPECXML | xml | Simple data list spec xml | |
| HASPARAMETERS | bit | yes | Has parameters |
| 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 | |
| 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 | |
| SITESECURITYTABLENAME | nvarchar(max) | yes | |
| SITESECURITYCOLUMNNAME | 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_SIMPLEDATALIST AS
with xmlnamespaces ('bb_appfx_simpledatalist' as tns, 'bb_appfx_commontypes' as common)
select
S.NAME,
S.DESCRIPTION,
S.SIMPLEDATALISTSPECXML.value('(tns:SimpleDataListSpec/@Author)[1]', 'nvarchar(500)') as AUTHOR,
S.IMPLEMENTATIONTYPENAME,
case S.IMPLEMENTATIONTYPE
when 0 then 'SP (' + S.PROCEDURENAME + ')'
when 1 then 'CLR (' + S.ASSEMBLYNAME + ', ' + S.CLASSNAME + ')'
end as IMPLEMENTATIONDESCRIPTION,
dbo.UFN_INSTALLEDPRODUCTS_TRANSLATELIST(S.SIMPLEDATALISTSPECXML.query('tns:SimpleDataListSpec/common:InstalledProductList')) as INSTALLEDPRODUCTSLIST,
dbo.UFN_INSTALLEDPRODUCTS_OPTIONALPRODUCTSINSTALLED(S.SIMPLEDATALISTSPECXML.query('tns:SimpleDataListSpec/common:InstalledProductList')) as INSTALLED,
S.SIMPLEDATALISTSPECXML.value('(tns:SimpleDataListSpec/@DefaultImageKey)[1]', 'nvarchar(max)') as DEFAULTIMAGEKEY,
coalesce(S.SIMPLEDATALISTSPECXML.value('(tns:SimpleDataListSpec/@NoSecurityRequired)[1]', 'bit'), 0) as NOSECURITYREQUIRED,
S.SIMPLEDATALISTSPECXML,
S.SIMPLEDATALISTSPECXML.exist('tns:SimpleDataListSpec/tns:Parameters') as HASPARAMETERS,
case S.SIMPLEDATALISTSPECXML.exist('tns:SimpleDataListSpec/tns:ResourceFile')
when 1 then
S.SIMPLEDATALISTSPECXML.value('(tns:SimpleDataListSpec/tns:ResourceFile/@AssemblyName)[1]', 'nvarchar(max)') + ', ' +
S.SIMPLEDATALISTSPECXML.value('(tns:SimpleDataListSpec/tns:ResourceFile/@ClassName)[1]', 'nvarchar(max)')
else null
end as RESOURCEFILE,
S.PROCEDURENAME,
case S.SIMPLEDATALISTSPECXML.exist('tns:SimpleDataListSpec/tns:SPSimpleList/common:CreateProcedureSQL')
when 1 then S.SIMPLEDATALISTSPECXML.value('(tns:SearchListSpec/tns:SPSimpleList/common:CreateProcedureSQL)[1]', 'nvarchar(max)')
else null
end as CREATEPROCEDURESQL,
S.ASSEMBLYNAME,
S.CLASSNAME,
case S.SIMPLEDATALISTSPECXML.exist('tns:SimpleDataListSpec/tns:Parameters/common:FormMetaData/common:FormUIComponent')
when 1 then
case S.SIMPLEDATALISTSPECXML.value('(tns:SimpleDataListSpec/tns:Parameters/common:FormMetaData/common:FormUIComponent/@FormUIComponentType)[1]', 'nvarchar(15)')
when 'CustomComponent' then 'Custom component'
else 'Default UI'
end
else
'None'
end as FORMUICOMPONENTTYPE,
S.SIMPLEDATALISTSPECXML.value('(tns:SimpleDataListSpec/tns:Parameters/common:FormMetaData/common:FormUIComponent/common:CustomComponentID/@AssemblyName)[1]', 'nvarchar(max)') as FORMCOMPONENTASSEMBLYNAME,
S.SIMPLEDATALISTSPECXML.value('(tns:SimpleDataListSpec/tns:Parameters/common:FormMetaData/common:FormUIComponent/common:CustomComponentID/@ClassName)[1]', 'nvarchar(max)') as FORMCOMPONENTCLASSNAME,
case S.SIMPLEDATALISTSPECXML.exist('tns:SimpleDataListSpec/tns:Parameters/common:FormMetaData/common:WebUIComponent')
when 1 then
case S.SIMPLEDATALISTSPECXML.exist('tns:SimpleDataListSpec/tns:Parameters/common:FormMetaData/common:WebUIComponent/common:WebUI/common:ExternalResource')
when 1 then 'External resource'
else 'Default UI'
end
else
'None'
end as WEBUICOMPONENTTYPE,
S.SIMPLEDATALISTSPECXML.value('(tns:SimpleDataListSpec/tns:Parameters/common:FormMetaData/common:WebUIComponent/common:UIModel/@AssemblyName)[1]', 'nvarchar(max)') as WEBUIMODELASSEMBLYNAME,
S.SIMPLEDATALISTSPECXML.value('(tns:SimpleDataListSpec/tns:Parameters/common:FormMetaData/common:WebUIComponent/common:UIModel/@ClassName)[1]', 'nvarchar(max)') as WEBUIMODELCLASSNAME,
S.SIMPLEDATALISTSPECXML.value('(tns:SimpleDataListSpec/tns:Parameters/common:FormMetaData/common:WebUIComponent/common:WebUI/common:ExternalResource/@Url)[1]', 'nvarchar(max)') as WEBUIEXTERNALRESOURCEURL,
S.ID,
ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
S.DATEADDED,
S.DATECHANGED,
S.TSLONG,
S.SPECUINAME,
S.UINAME,
S.SIMPLEDATALISTSPECXML.value('(tns:SimpleDataListSpec/tns:SiteSecurity/@TableName)[1]', 'nvarchar(max)') as SITESECURITYTABLENAME,
S.SIMPLEDATALISTSPECXML.value('(tns:SimpleDataListSpec/tns:SiteSecurity/@ColumnName)[1]', 'nvarchar(max)') as SITESECURITYCOLUMNNAME
/*#EXTENSION*/
from dbo.SIMPLEDATALISTCATALOG as S
left outer join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = S.ADDEDBYID
left outer join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = S.CHANGEDBYID