SQL statement help - Is this even possible

Question

SQL statement help - Is this even possible

mw_sql_developer

SSCoach

Points: 19445
More actions
September 28, 2016 at 10:45 am

#400797

Select top 100 * FROM ReportServer.dbo.Catalog
I like to know whether it is possible to write a SQL statement that will return
1. Report name, 2. sp_name ( if using sp for obtaining output ) , 3. tbl or view name ( in case it uses a query that calls a table or view )

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 1

I don't completely understand what you're asking? Can you expand on what it is that you expect to return? What's the data structure you're pulling information from?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

mw_sql_developer SSCoach Points: 19445 More actions · Answer 2

Select name FROM ReportServer.dbo.Catalog where type = 2

--This gives you the list of all the SSRS reports in your Reporting Server. Agree ?

-- I am looking forqward to expanding this query, ( joning it with more tables ) and

--finidng out which sps are used by the report, or if the report uses a query then what view or table

--does the report use

Sue_H SSC Guru Points: 90860 More actions · Answer 3

mw112009 (9/28/2016)
Select top 100 * FROM ReportServer.dbo.Catalog
I like to know whether it is possible to write a SQL statement that will return
1. Report name, 2. sp_name ( if using sp for obtaining output ) , 3. tbl or view name ( in case it uses a query that calls a table or view )

The catalog table isn't a normal relational table so it's not a basic select to get some information out of it, the content column has some of the information you are looking for. You'll need to cast that column to varbinary and then again to xml and then shred the xml to get some of the information. To see the xml, you can execute something like:

SELECT C.Name AS ReportName,

CONVERT(xml, CONVERT(varbinary(max), C.Content)) As XMLDef

FROM ReportServer.dbo.Catalog AS C

WHERE C.Type = 2

If you look through the XML, you can see that command text for the report is in there so that's most of what you are looking for.

This script has what you are looking for: https://gallery.technet.microsoft.com/scriptcenter/List-datasets-with-command-ddffefde

I think you probably need to change the namespace to

http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition

That all should give you some idea of how to do what you are trying to accomplish.

Sue

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 4

mw112009 (9/28/2016)
Select name FROM ReportServer.dbo.Catalog where type = 2
--This gives you the list of all the SSRS reports in your Reporting Server. Agree ?
-- I am looking forqward to expanding this query, ( joning it with more tables ) and
--finidng out which sps are used by the report, or if the report uses a query then what view or table
--does the report use

Ah, this is SSRS. No, that wasn't clear to me. I'll bow out now because I don't have much knowledge in that area at all.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

mw_sql_developer SSCoach Points: 19445 More actions · Answer 5

Sue_H:

I know how to do the XL Query stuff.

Unfortunately that still leaves us having to do further scanning to get the name of the table etc.

I have pasted a section from the XML. If you see we have to first check whether the report has a query and then we have to scan the entire section between the <CommandText> and </CommandText> part and then we don't know whether MHPEDI834 is an table or view ?

<Query><DataSourceName>TransferDB</DataSourceName>

<Value>=Parameters!ID.Value</Value></QueryParameter></QueryParameters>

SELECT ID, MemberEDIData

FROM MHPEDI834

WHERE (ID = @ID)

</CommandText>

</Query><Fields>

<DataField>ID</DataField><

Sue_H SSC Guru Points: 90860 More actions · Answer 6

mw112009 (9/28/2016)
Sue_H:
I know how to do the XL Query stuff.
Unfortunately that still leaves us having to do further scanning to get the name of the table etc.
I have pasted a section from the XML. If you see we have to first check whether the report has a query and then we have to scan the entire section between the <CommandText> and </CommandText> part and then we don't know whether MHPEDI834 is an table or view ?
<Query><DataSourceName>TransferDB</DataSourceName>
<QueryParameters><QueryParameter Name="@ID">
<Value>=Parameters!ID.Value</Value></QueryParameter></QueryParameters>
<CommandText>
SELECT ID, MemberEDIData
FROM MHPEDI834
WHERE (ID = @ID)
</CommandText>
</Query><Fields>
<Field Name="ID">
<DataField>ID</DataField><

Correct as Reporting Services has no knowledge of all the details of the data sources used. It wouldn't know whether something is a view or a table, it just knows the commands that are executed against the data source since that is what is used when rendering the report.

Sue