SQL query that returns data table columns pre formatted for use in developing a .obsprot file. - NPS-ARCN-CAKN/YBLO-Observation-Protocol-ObsProt GitHub Wiki

-- The view below will return data table columns in various JSON object formats that may be useful -- in developing a .obsprot Park Observer protocol file. SELECT c.name 'Column Name', t.Name 'Data type' ,'{"name": "' + c.name + '","type": ' + CASE WHEN t.name = 'int' then '100' WHEN t.name = 'varchar' then '700' WHEN t.name = 'bit' then '800' ELSE '700' END + '},' as Attribute

,	
CASE 
	WHEN t.name = 'bit' then '{"key": "' + c.name + '","bind": "boolValue:' + c.name + '","type": "QBooleanElement","title": "' + c.name + '?","boolValue": 0},' 
	WHEN t.name = 'varchar' or t.name='datetime' then '{"key": "' + c.name + '","bind": "textValue:' + c.name + '","type": "QEntryElement","title": "' + c.name + '"},' 
	WHEN t.name = 'int' then '{"key": "' + c.name + '","bind": "numberValue:' + c.name + '","type": "QIntegerElement","title": "' + c.name + '"},' 
	ELSE '????' 
END
 as Element
,	
'{"type": "QLabelElement","title": "' + c.name + '"},{"key": "' + c.name + '","bind": "selectedItem:' + c.name + '","type": "QSegmentedElement","items": ["Option0","Option1","Option2"]},'
as SegmentedElement

,'{"type": "QRadioElement","title": "' + c.name + '","key": "' + c.name + '","bind": "selectedItem:' + c.name + '","items": ["Option1","Option2"]},' as RadioElement

,
c.max_length 'Max Length',
c.[precision] ,
c.scale ,
c.is_nullable,
ISNULL(i.is_primary_key, 0) 'Primary Key'

FROM
sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id WHERE c.object_id = OBJECT_ID('LakeObservations') and c.name not in ('CertificationLevel','CertificationDate','CertifiedBy','certificationnotes','SourceFile','enteredby','entereddate','proofedby','proofeddate','lakeobsid','observationdate')