Script to view all parameters in all reports found in the Reporting Services reports catalog table.
;WITH [params] AS
(
SELECT
[r].[ReportPath], [r].[ReportName],
[p].[Param].[value]('Name[1]', 'nvarchar(500)') AS [ParameterName],
[p].[Param].[value]('Type[1]', 'nvarchar(500)') AS [Type],
[p].[Param].[value]('Nullable[1]', 'varchar(10)') AS [Nullable],
[p].[Param].[value]('AllowBlank[1]', 'varchar(10)') AS [AllowBlank],
[p].[Param].[value]('MultiValue[1]', 'varchar(10)') AS [MultiValue],
[p].[Param].[value]('UsedInQuery[1]', 'varchar(10)') AS [UsedInQuery],
[p].[Param].[value]('State[1]', 'varchar(100)') AS [State],
[p].[Param].[value]('PromptUser[1]', 'varchar(10)') AS [PromptUser],
[p].[Param].[value]('DynamicPrompt[1]', 'varchar(10)') AS [DynamicPrompt],
[p].[Param].[value]('Prompt[1]', 'varchar(100)') AS [Prompt],
[p].[Param].[value]('DynamicValidValues[1]', 'varchar(10)') AS [DynamicValidValues],
[p].[Param].[value]('DynamicDefaultValue[1]', 'varchar(10)') AS [DynamicDefaultValue],
[p].[Param].[exist]('Dependencies[1]/Dependency') AS [HasDependencies],
[p].[Param].[exist]('DefaultValues[1]/Value') AS [HasDefaultValues],
[p].[Param].[exist]('Values[1]/Value') AS [HasValues],
[p].[Param].[value]('Dependencies[1]', 'nvarchar(4000)') AS [Dependencies],
[p].[Param].[value]('DefaultValues[1]', 'nvarchar(4000)') AS [DefaultValues],
[p].[Param].[value]('Values[1]', 'nvarchar(4000)') AS [Values]
FROM (SELECT
[c].[Path] [ReportPath], [c].[Name] [ReportName],
CONVERT(XML, [c].[Parameter]) AS [ParamsXML]
FROM [dbo].[Catalog] [c]
WHERE [c].[Type] = 2
) [r]
CROSS APPLY [r].[ParamsXML].[nodes]('/Parameters/Parameter') AS [p]([Param])
)
SELECT
[ReportPath], [ReportName],
[ParameterName], [Type], [Nullable], [AllowBlank], [MultiValue],
[UsedInQuery], [State],
[PromptUser], [DynamicPrompt], [Prompt],
[DynamicValidValues], [DynamicDefaultValue],
[HasDependencies], [HasDefaultValues], [HasValues],
[Dependencies], [DefaultValues], [Values]
FROM [params]
ORDER BY [params].[ReportPath]