Question
How can you snag the report or other SQL queries from the database

Answer

The SQL queries from reports are stored in the State column in the Item table. These are the step to follow to snag the SQL queries

1.) Open Query Analyzer
2.) Change the results of a query then is run in Query Analyzer

a.)    Open menu Tools > Options and select the Results tab.

b.)    Change the value Maximum characters per column; from 256 (default) to say 8000.

c.)    Select the button OK

3.) Run the following SQL query. This will snap the queries from reports. This includes the all level queries (0, 1, 2 …) and parameter values/queries as well.

-- This SQL query will snap the report name and the queries section of the ntext data

select

[Report Name] = it.[name],

[Query String] = case
when (charindex('</item>', it.[state]) > 0 ) then
       substring(it.[state], charindex('<queries>', it.[state]),charindex('</queries>', it.[state]))
else substring(it.[state], charindex('<queries>', it.[state]), 4000)
end

from item it join vReport vr on it.[guid] = vr.[guid]

where it.[state] is not null

   and it.[Name] like '%'

   and vr.[Description] like '%'

order by 1

4.) Save the SQL query results to a csv file.

This is a query you can use for SQL server 2005...

select i.[guid], i.[name], convert(xml,i.[state]), cl.[Type]
from Item i
join ItemClass ic on ic.[Guid] = i.[Guid]
join Class cl on cl.[Guid] = ic.[ClassGuid]
where 1 = 1
  and i.[name] like '%%'
  and i.[Attributes] = 0
  and [state] is not null
  and i.[name] not in ('',null)
order by  i.[name], cl.[Type]

The link in the query will open a new tab with the state column in XML.

The queries with (<,>,@...) will be translated to a safe xml data, so you will need to translate it back if you wish to use it.

Notes: