• ’pokazuje wszystkie pola z danego katalogu oprocz inherited

select 

ufd.id, pa.parent,ufd.area, pa.name asFolder, ufd.label, ufd.alias, ufd.run_time_field, otFieldTypes.field_type,

case 

 when ufd.[type] = 254 then 'ReferenceList’+ Convert(nvarchar(50),  ufd.id) 

 else” 

 end as RefTable,

case

 when ufd.[type] = 254 then 'ReferenceList’+ Convert(nvarchar(50), ufd.id) + ’.Request = '+ rtrim(pa.name) + ’ Object’

 else”

 end as Description_Request,

case 

 when ufd.[type] = 254 then 'ReferenceList’+ Convert(nvarchar(50), ufd.id) + ’.Reference = '+ rtrim(ufd.label) + ’ Object’

 else ” 

 end as Description_Reference 

from 

 UserFieldDef ufd left outer join ProblemArea pa on ufd.area = pa.id left outer join 

  (

   select 

     id,area,

     case

       when [type] = 1 then 'otUfBoolean’

       when [type] = 2 then 'otUfByte’

       when [type] = 3 then 'otUfInteger’

       when [type] = 4 then 'otUfLong’

       when [type] = 5 then 'otUfCurrency’

       when [type] = 6 then 'otUfSingle’

       when [type] = 7 then 'otUfDouble’

       when [type] = 8 then 'otUfDate’

       when [type] = 10 then 'otUfText’

       when [type] = 12 then 'otUfMemo’

       when [type] = 50 then 'otUfAutonumber’

       when [type] = 51 then 'otUfDropDown’

       when [type] = 52 then 'otUfStampedMemo’

       when [type] = 53 then 'otUfWorkflow’

       when [type] = 54 then 'otUfSchedule’

       when [type] = 55 then 'otUfAttachments’

       when [type] = 250 then 'otUfRefUser’

       when [type] = 251 then 'otUfRefCustomer’

       when [type] = 252 then 'otUfRefRequest’

       when [type] = 253 then 'OtUfRefInvItem’

       when [type] = 254 then 'OtUfRefList’

   else 

      Cast([type] as nvarchar(10)) 

   end as field_type 

 from UserFieldDef) 

  otFieldTypes on otFieldTypes.id = ufd.id and otFieldTypes.area = ufd.area 

     where ufd.area = (select id from ProblemArea where alias =’Incidents’) order by label

• ’polaczenie – pole w katalogu z powyzszego zapytania a jego nazwa z tabeli UserFields_area

SELECT 

      [fieldname]

  FROM [OT_Development_10.2].[dbo].[RunTimeFieldDef]

    where id=300 'run_time_field z powyzszego zapytania

• ’wszystkie requests sa tutaj z danego katalogu (trzeba patrzec nazwe kolumny z powyzszego zapytania)

SELECT  * 

  FROM [OT_Development_10.2].[dbo].[UserFields1] 'gdzie 1=to jest area z pierwszego zapytania

• ’grzebanie po historii

SELECT TOP (1000) h.[id]

      ,[report]

      ,[change_date]

      ,user1.display_name as username

      ,[attribute]

      ,[user_field]

  ,u.alias

      ,[old_value]

      ,[new_value]

      ,[state]

      ,[format]

      ,[guest]

  FROM [OT_Development_10.2].[dbo].[History] h,UserFieldDef u,User_ user1

  where h.user_field=u.id 

  and user1.id=user_

  and u.area in (1,38)  'tutaj trzeba wziazc id z PRoblemArea – te dwa odp.ServiceDesk oraz Tickets 

  and report=603709 'by wydobyc te id trzeba select request from UserFields1

  order by 3