正文
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
CRLF
(
CRLF
SELECT ItemID ,
CRLF
Name ,
CRLF
[Type] ,
CRLF
CASE Type
CRLF
WHEN 2 THEN 'Report'
CRLF
WHEN 5 THEN 'Data Source'
CRLF
WHEN 7 THEN 'Report Part'
CRLF
WHEN 8 THEN 'Shared Dataset'
CRLF
ELSE 'Other'
CRLF
END AS TypeDescription ,
CRLF
CONVERT(VARBINARY(MAX), Content) AS Content
CRLF
FROM ReportServer.dbo.Catalog
CRLF
WHERE Type IN ( 2, 5, 7, 8 )
CRLF
),
CRLF
ItemContentNoBOM AS
CRLF
(
CRLF
SELECT ItemID ,
CRLF
Name ,
CRLF
[Type] ,
CRLF
TypeDescription ,
CRLF
CASE WHEN LEFT(Content, 3) = 0xEFBBBF
CRLF
THEN CONVERT(VARBINARY(MAX), SUBSTRING(Content, 4,
CRLF
LEN(Content)))
CRLF
ELSE Content
CRLF
END AS Content
CRLF
FROM ItemContentBinaries
CRLF
)
CRLF
,ItemContentXML AS
CRLF
(
CRLF
SELECT
CRLF
ItemID,Name,[Type],TypeDescription
CRLF
,CONVERT(xml,Content) AS ContentXML
CRLF
FROM ItemContentNoBOM
CRLF
)
CRLF
SELECT
CRLF
ItemID,Name,[Type],TypeDescription,ContentXML
CRLF
,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
CRLF
,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
CRLF
CRLF
FROM ItemContentXML
CRLF
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)
CRLF
WHERE Query.value('(./*:CommandText/text())[1]','nvarchar(max)') LIKE '%SQL Script Content%';
CRLF
不过这个SQL的性能实在慢的让人抓狂。如果有多个SQL需要定位，实在是一件折磨人的事情 ，我们可以将上面结果放入一张中间表或全局临时表，然后就可以快速、反复的定位SQL来自那种报表了。
WITH ItemContentBinaries AS
CRLF
(
CRLF
SELECT ItemID ,
CRLF
Name ,
CRLF
[Type] ,
CRLF
CASE Type
CRLF
WHEN 2 THEN 'Report'
CRLF
WHEN 5 THEN 'Data Source'
CRLF
WHEN 7 THEN 'Report Part'
CRLF
WHEN 8 THEN 'Shared Dataset'
CRLF
ELSE 'Other'
CRLF
END AS TypeDescription ,
CRLF
CONVERT(VARBINARY(MAX), Content) AS Content
CRLF
FROM ReportServer.dbo.Catalog
CRLF
WHERE Type IN ( 2, 5, 7, 8 )
CRLF
),
CRLF
ItemContentNoBOM AS
CRLF
(
CRLF
SELECT ItemID ,
CRLF
Name ,
CRLF
[Type] ,
CRLF
TypeDescription ,
CRLF
CASE WHEN LEFT(Content, 3) = 0xEFBBBF
CRLF
THEN CONVERT(VARBINARY(MAX), SUBSTRING(Content, 4,
CRLF
LEN(Content)))
CRLF
ELSE Content
CRLF
END AS Content
CRLF
FROM ItemContentBinaries
CRLF
)
CRLF
,ItemContentXML AS
CRLF
(
CRLF
SELECT
CRLF
ItemID,Name,[Type],TypeDescription
CRLF
,CONVERT(xml,Content) AS ContentXML
CRLF
FROM ItemContentNoBOM
CRLF
)
CRLF
SELECT
CRLF
ItemID,Name,[Type],TypeDescription,ContentXML
CRLF
,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
CRLF
,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
CRLF
INTO ##ReportContent
CRLF
FROM ItemContentXML
CRLF
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query);
CRLF
CRLF
SELECT * FROM ##ReportContent
CRLF
WHERE CommandText LIKE '%使用报表的部分SQL来替换%'
CRLF
如下样例所示，已经知道报表的名字，以及报表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/
