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