select name from dbo.tblcontenttype where pkid in
(
  select
  dbo.tblcontent.fkcontenttypeid
  from
  dbo.tblcontent
  inner join
  dbo.tblcontentlanguage
  on dbo.tblcontent.pkid=dbo.tblcontentlanguage.fkcontentid
  and dbo.tblcontent.fkmasterlanguagebranchid=dbo.tblcontentlanguage.fklanguagebranchid
  inner join
  dbo.tblworkcontent
  on dbo.tblcontentlanguage.version=dbo.tblworkcontent.pkid
  where
  dbo.tblcontent.deleted=0
  and dbo.tblcontentlanguage.status=4
  and ((getdate()>dbo.tblcontentlanguage.startpublish and dbo.tblcontentlanguage.stoppublish is null) or (getdate() between dbo.tblcontentlanguage.startpublish and dbo.tblcontentlanguage.stoppublish))
  and dbo.tblcontent.fkcontenttypeid in (select tpt.pkid from dbo.tblcontenttype tpt where tpt.contenttype=1)
  group by
  dbo.tblcontent.fkcontenttypeid
)
order by name asc

SELECT DISTINCT LOWER(name) FROM tblpropertydefinition

select name from dbo.tblcontenttype where pkid in 
(
  select 
  dbo.tblcontent.fkcontenttypeid
  from
  dbo.tblcontent
  inner join
  dbo.tblcontentlanguage
  on dbo.tblcontent.pkid=dbo.tblcontentlanguage.fkcontentid
  and dbo.tblcontent.fkmasterlanguagebranchid=dbo.tblcontentlanguage.fklanguagebranchid
  inner join
  dbo.tblworkcontent
  on dbo.tblcontentlanguage.version=dbo.tblworkcontent.pkid
  where
  dbo.tblcontent.deleted=0
  and dbo.tblcontentlanguage.status=4
  and ((getdate()>dbo.tblcontentlanguage.startpublish and dbo.tblcontentlanguage.stoppublish is null) or (getdate() between dbo.tblcontentlanguage.startpublish and dbo.tblcontentlanguage.stoppublish))
  and dbo.tblcontent.fkcontenttypeid not in (select tpt.pkid from dbo.tblcontenttype tpt where tpt.name in ('SysRoot','SysRecycleBin'))
  and dbo.tblcontent.fkcontenttypeid in (select tpt.pkid from dbo.tblcontenttype tpt where tpt.contenttype=0)
  group by 
  dbo.tblcontent.fkcontenttypeid
)
order by name asc        

select name from dbo.tblcontenttype where pkid in 
(
  select 
  dbo.tblcontent.fkcontenttypeid
  from
  dbo.tblcontent
  inner join
  dbo.tblcontentlanguage
  on dbo.tblcontent.pkid=dbo.tblcontentlanguage.fkcontentid
  and dbo.tblcontent.fkmasterlanguagebranchid=dbo.tblcontentlanguage.fklanguagebranchid
  inner join
  dbo.tblworkcontent
  on dbo.tblcontentlanguage.version=dbo.tblworkcontent.pkid
  where
  dbo.tblcontent.deleted=0
  and dbo.tblcontentlanguage.status=4
  and ((getdate()>dbo.tblcontentlanguage.startpublish and dbo.tblcontentlanguage.stoppublish is null) or (getdate() between dbo.tblcontentlanguage.startpublish and dbo.tblcontentlanguage.stoppublish))
  and dbo.tblcontent.fkcontenttypeid not in (select tpt.pkid from dbo.tblcontenttype tpt where tpt.name in ('SysContentFolder','SysContentAssetFolder'))
  and dbo.tblcontent.fkcontenttypeid in (select tpt.pkid from dbo.tblcontenttype tpt where tpt.contenttype=2)
  group by 
  dbo.tblcontent.fkcontenttypeid
)
order by name asc        

select name from dbo.tblpagetype where pkid in
(
  select
  dbo.tblpage.fkpagetypeid
  from
  dbo.tblpage
  inner join
  dbo.tblpagelanguage
  on dbo.tblpage.pkid=dbo.tblpagelanguage.fkpageid
  and dbo.tblpage.fkmasterlanguagebranchid=dbo.tblpagelanguage.fklanguagebranchid
  inner join
  dbo.tblworkpage
  on dbo.tblpagelanguage.PublishedVersion=dbo.tblworkpage.pkid
  where
  dbo.tblpage.deleted=0
  --and dbo.tblpagelanguage.status=4
  and ((getdate()>dbo.tblpagelanguage.startpublish and dbo.tblpagelanguage.stoppublish is null) or (getdate() between dbo.tblpagelanguage.startpublish and dbo.tblpagelanguage.stoppublish))
  and dbo.tblpage.fkpagetypeid not in (select tpt.pkid from dbo.tblpagetype tpt where tpt.name in ('SysRoot','SysRecycleBin'))
  --and dbo.tblpage.fkpagetypeid in (select tpt.pkid from dbo.tblpagetype tpt where tpt.pagetype=0)
  group by
  dbo.tblpage.fkpagetypeid
)
order by name asc