# 从 SSRS 数据库中获取信息

# 查看报表访问记录

USE [ReportServer];
GO

SELECT  C.Name                         AS ReportName
       ,E.ReportID                     AS ReportID
       ,E.UserName                     AS UserName
       ,E.Format                       AS Format
       ,E.Parameters                   AS Parameters
       ,E.TimeStart                    AS TimeStart
       ,E.TimeEnd                      AS TimeEnd
       ,E.TimeDataRetrieval*1.0/1000   AS TimeDataRetrieval
       ,E.TimeProcessing*1.0/1000      AS TimeProcessing
       ,E.TimeRendering*1.0/1000       AS TimeRendering
       ,DATEDIFF(SECOND, TimeStart, TimeEnd)
                                       AS  CostTime
FROM ReportServer.dbo.ExecutionLog E WITH(NOLOCK)
INNER JOIN ReportServer.dbo.Catalog C WITH(NOLOCK)ON E.ReportID = C.ItemID
--WHERE C.Name ='WF_MarkerRoom_Report'
--   AND E.TimeStart > CAST('2014-12-25 00:00' AS DATETIME)
--  AND E.TimeStart <= CAST('2014-12-25 12:00' AS DATETIME)
ORDER BY TimeStart DESC

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

# 查看Report Server数据库中所有的表

Select Name,Create_Date,Modify_Date from sys.tables

# 查看某报表的详细信息

Declare @Namespace NVARCHAR(500)
Declare @SQL   VARCHAR(max)
Declare  @ReportName NVARCHAR(850)
SET @ReportName='Simple Test Report.rdl'

SELECT @Namespace= SUBSTRING(
               x.CatContent
              ,x.CIndex
              ,CHARINDEX('"',x.CatContent,x.CIndex+7) - x.CIndex
            )
  FROM
 (
     SELECT CatContent = CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)))
            ,CIndex    = CHARINDEX('xmlns="',CONVERT(NVARCHAR(MAX),CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content))))
       FROM Reportserver.dbo.Catalog C
      WHERE C.Content is not null
        AND C.Type  = 2
 ) X

SELECT @Namespace = REPLACE(@Namespace,'xmlns="','') + ''
SELECT @SQL = 'WITH XMLNAMESPACES ( DEFAULT ''' + @Namespace +''', ''http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'' AS rd )
SELECT  ReportName		 = name
       ,DataSourceName	 = x.value(''(@Name)[1]'', ''VARCHAR(250)'')
       ,DataProvider	 = x.value(''(ConnectionProperties/DataProvider)[1]'',''VARCHAR(250)'')
       ,ConnectionString = x.value(''(ConnectionProperties/ConnectString)[1]'',''VARCHAR(250)'')
  FROM (  SELECT top 1 C.Name,CONVERT(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML
           FROM  ReportServer.dbo.Catalog C
          WHERE  C.Content is not null
            AND  C.Type  = 2
            AND  C.Name  = ''' + @ReportName + '''
  ) a
  CROSS APPLY reportXML.nodes(''/Report/DataSources/DataSource'') r ( x )
ORDER BY name ;'

EXEC(@SQL)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# 查看报表的参数信息

SELECT Name as ReportName
		,ParameterName = Paravalue.value('Name[1]', 'VARCHAR(250)')
	   ,ParameterType = Paravalue.value('Type[1]', 'VARCHAR(250)')
	   ,ISNullable = Paravalue.value('Nullable[1]', 'VARCHAR(250)')
	   ,ISAllowBlank = Paravalue.value('AllowBlank[1]', 'VARCHAR(250)')
	   ,ISMultiValue = Paravalue.value('MultiValue[1]', 'VARCHAR(250)')
	   ,ISUsedInQuery = Paravalue.value('UsedInQuery[1]', 'VARCHAR(250)')
	   ,ParameterPrompt = Paravalue.value('Prompt[1]', 'VARCHAR(250)')
	   ,DynamicPrompt = Paravalue.value('DynamicPrompt[1]', 'VARCHAR(250)')
	   ,PromptUser = Paravalue.value('PromptUser[1]', 'VARCHAR(250)')
	   ,State = Paravalue.value('State[1]', 'VARCHAR(250)')
 FROM (
		 SELECT top 1 C.Name,CONVERT(XML,C.Parameter) AS ParameterXML
		   FROM  ReportServer.dbo.Catalog C
		  WHERE  C.Content is not null
		AND  C.Type  = 2
		AND  C.Name  =  'Simple Test Report.rdl'
	  ) a
CROSS APPLY ParameterXML.nodes('//Parameters/Parameter') p ( Paravalue )

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

# 查看报表的所有者

Select C.Name,C.Path,U.UserName,C.CreationDate,C.ModifiedDate from Catalog C
INNER Join Users U ON C.CreatedByID=U.UserID
Where C.Name ='Simple Test Report.rdl'

1
2
3

# 查询某个对象在Report Server中的关连信息

With Reports
AS
(
Select Name as ReportName,CONVERT(Varchar(Max),CONVERT(VARBINARY(MAX),Content)) AS ReportContent from
Catalog Where Name is NOT NULL
)
Select ReportName from Reports Where ReportContent like '%tablename%'

1
2
3
4
5
6
7

# 存储在数据库中RDL文件的内容

Select Name as ReportName,CONVERT(XML,CONVERT(VARBINARY(MAX),Content)) AS ReportContent from
Catalog Where Name ='Simple Test Report.rdl'

1
2

# 获取Report Server的配置信息

Select Name,Value from ConfigurationInfo

# 查看角色

Select RoleName,Description from Roles

# 查看Report Server所在服务器的实例信息

Select MachineName,InstallationID,InstanceName,Client,PublicKey,SymmetricKey from Keys
Where MachineName IS NOT NULL

1
2