SQL Server 如何通过SQL语句定位SSRS中的具体报表

在一些IT技术人员的推广、简单培训后，公司很多部门都有一些非IT技术人员参与开发各自需求的Reporting Service报表。原因很简单，罗列出来的原因大概有这样一些：

 

IT部门的考量：

1：IT部门这边工作量很大，跟进各个项目都力不从心。不想腾出精力和时间来解决各个部门层出不穷的报表需求。

2：IT技术人员可能对各个部门的业务的理解和那些精通业务的员工有一定的差距。业务人员才是真正懂得应用需求的核心人员。

3：这些报表的需求变跟和后续维护实在是一个不小的工作量。IT的人手、资源实在有些不足。

4：这些零零散散的报表体现不了工作量，体现不了绩效。原因你懂的。

………………………………………………………………………………

 

业务部门考量：

1：公司各个部门确实需要各类报表，跟进生产进度、调整生产计划，作出相关决策。这个需求的的确确是刚性需求。而且有利于提高生效效率。

2：业务人员虽然精通业务，仅仅熟悉制作Excel报表。对IT技术不了解，但是经过培训、推广后，发现Reporting Service的报表确实开发简单、而且图文并茂，美观大方。最重要的是可以重复使用，而且可以订阅、推送，大大节省了他们制作报表的时间和工作量。所以学习制作报表的热情和激情高涨

3：他们提出的需求不能得到IT部门的快速响应。有时候一拖就是一天或者几天。而需求总是在变化，他们迫切希望自己掌控这些变化。

…………………………………………………………………………………………………………………………….

 

结果他们“郎有情妾有意”一拍即合，结果给我整出无数的琐碎事情：一来很多人申请Reporting Service的相关权限，很多人发布更新报表。事情倒不复杂，只是琐碎繁杂，烦不胜烦，只能将一些权限下放。这个问题解决了，但是随之而来的一个更大的问题，那些没有经过专业培训的业务人员写出的SQL实在是让人大跌眼镜。有时候严重影响数据库性能。我们通过监控工具能定位到是那个Reporting Service报表发出的问题SQL，但是要如何定位到具体的报表，这样才能找到报表的Owner，督促其修改、优化SQL。否则即使我们定位了问题SQL以及知道如何优化，但是不能修改对应的报表，也只能看着问题重演。如果只是简单的将SQL发给这么一大批人，让他们自己去甄别，刷选，这个沟通的成本太高，而且效率低下，效果非常差。

 

搜索了一些关于Reporting Service中报表的资料，我们知道Reporting Service报表的内容都保存在ReportServer这个数据库的dbo.Catalog表中，但是官方没有关于Catalog这些系统表的相关文档。仅仅是一些对SSRS感兴趣的人做了一些深入研究，相关资料如下

 

关于Type字段的值代表的意义：

1 = Folder

2 = Report

3 = Resources

4 = Linked Report

5 = Data Source

6 = Report Model

7 = Report Part (SQL 2008 R2, unverified)

8 = Shared Dataset (SQL 2008 R2)

 

报表的XML信息保存在Catalog的Content字段中，但是Content的数据类型为Image（这个相当纳闷，不清楚为什么是这样一个设计？），如下所示，我们可以做一个转换

 

我们在转换成XML的文本中就能找到对应的SQL，节点一般为为/Report/DataSets/DataSet/Query/CommandText如下截图所示：

 

 

将报表内容转换为XML后，需要从XML中模糊搜索才能定位SQL出自那张报表，如下所示

 

WITH ItemContentBinaries AS
(
  SELECT    ItemID ,
            Name ,
            [Type] ,
            CASE Type
              WHEN 2 THEN 'Report'
              WHEN 5 THEN 'Data Source'
              WHEN 7 THEN 'Report Part'
              WHEN 8 THEN 'Shared Dataset'
              ELSE 'Other'
            END AS TypeDescription ,
            CONVERT(VARBINARY(MAX), Content) AS Content
  FROM      ReportServer.dbo.Catalog
  WHERE     Type IN ( 2, 5, 7, 8 )
),
ItemContentNoBOM AS
(
  SELECT    ItemID ,
            Name ,
            [Type] ,
            TypeDescription ,
            CASE WHEN LEFT(Content, 3) = 0xEFBBBF
                 THEN CONVERT(VARBINARY(MAX), SUBSTRING(Content, 4,
                                                        LEN(Content)))
                 ELSE Content
            END AS Content
  FROM      ItemContentBinaries
)
,ItemContentXML AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CONVERT(xml,Content) AS ContentXML
 FROM ItemContentNoBOM
)
SELECT
     ItemID,Name,[Type],TypeDescription,ContentXML
    ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
    ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText

FROM ItemContentXML
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)
WHERE Query.value('(./*:CommandText/text())[1]','nvarchar(max)') LIKE  '%SQL Script Content%';

不过这个SQL的性能实在慢的让人抓狂。如果有多个SQL需要定位，实在是一件折磨人的事情，我们可以将上面结果放入一张中间表或全局临时表，然后就可以快速、反复的定位SQL来自那种报表了。

WITH ItemContentBinaries AS
(
  SELECT    ItemID ,
            Name ,
            [Type] ,
            CASE Type
              WHEN 2 THEN 'Report'
              WHEN 5 THEN 'Data Source'
              WHEN 7 THEN 'Report Part'
              WHEN 8 THEN 'Shared Dataset'
              ELSE 'Other'
            END AS TypeDescription ,
            CONVERT(VARBINARY(MAX), Content) AS Content
  FROM      ReportServer.dbo.Catalog
  WHERE     Type IN ( 2, 5, 7, 8 )
),
ItemContentNoBOM AS
(
  SELECT    ItemID ,
            Name ,
            [Type] ,
            TypeDescription ,
            CASE WHEN LEFT(Content, 3) = 0xEFBBBF
                 THEN CONVERT(VARBINARY(MAX), SUBSTRING(Content, 4,
                                                        LEN(Content)))
                 ELSE Content
            END AS Content
  FROM      ItemContentBinaries
)
,ItemContentXML AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CONVERT(xml,Content) AS ContentXML
 FROM ItemContentNoBOM
)
SELECT
     ItemID,Name,[Type],TypeDescription,ContentXML
    ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
    ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
INTO ##ReportContent
FROM ItemContentXML
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query);

SELECT * FROM ##ReportContent
WHERE  CommandText LIKE '%使用报表的部分SQL来替换%'

 

如下样例所示，已经知道报表的名字，以及报表ItemID，如果你想知道报表的详细路径，通过ItemID查询ReportServer.dbo.Catalog即可得到你想要的路径信息。

 

 

参考资料：

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/60dd3392-42d8-4dc4-b8e6-15e9aeaad29e/table-explaination-for-dbocatalog-table-in-reportserver-database?forum=sqlreportingservices

http://bretstateham.com/extracting-ssrs-report-rdl-xml-from-the-reportserver-database/