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
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)
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 )
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'
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%'
Select Name as ReportName,CONVERT(XML,CONVERT(VARBINARY(MAX),Content)) AS ReportContent from
Catalog Where Name ='Simple Test Report.rdl'
Select Name,Value from ConfigurationInfo
Select RoleName,Description from Roles
Select MachineName,InstallationID,InstanceName,Client,PublicKey,SymmetricKey from Keys
Where MachineName IS NOT NULL