question

One way of doing it would be to create a table with just one column to store the value of your procedures, one record per proc\_name. Lets say that you have such table. In this case you can just join the two (not so sure that the query will be efficient, but it will work): select distinct c.[name], c.[path], cast(cast(c.[content] as varbinary(max)) as varchar(max)) [content] from ReportServer.dbo.Catalog c inner join your_db.dbo.your_table t on cast(cast(c.[content] as varbinary(max)) as varchar(max)) like '%' + t.your_column_name + '%'; Let me know if your list is not kept in the table, but is instead serialized as xml or a delimited string of procedure names, and I can add an appropriate snippet. <\!-- **Begin Edit** I have to edit my answer once more due to a small flaw in the script discovered as a result of the conversation in the comments to @Fathrjack's answer. The problem with applying .value directly to the xml content is that the method requires a singleton as its first parameter, so it will always return one record per report. This works most times, but if the report references more than one stored procedure then only the first one will show up in results. Additionally, if the first command text of the report using multiple procs/scrips is actually a plain valilla SQL statement then the result will not show any procedures at all. This needs to be addressed. The final query is listed below the original one (now marked as do not use). Here is the snippet you can use to get some useful information from the content column of the catalog table. Please note that this script will only work if the values in the Content column of the Catalog table are indeed convertible to valid xml. Since the actual data type of the Content column is image (they probably meant to design it as varbinary(max) but ended up with outdated image instead), it is possible that some of the column values are xml-looking, but do contain some invalid xml characters. In other words, it is possible that some of the values actually contain invalid xml. This can occasionally happen when there is a corrupted embedded picture in the actual report definition. Please modify the name of both ReportServer database and the name of the database where your procs callable by reports are actually stored, and the value of xmlns namespace to match yours (I only have SQL Server 2005 handy, so the default namespace of the reports is 2005 specific). Finally, I have to disclose that I simply stole Håkan Winther's idea to use the sys.procedures catalog view instead of worrying how to pass the list of the procedures to the query :) Here is the script (the one with the flaw described above, so don't use this one): with reports ([name], [path], DataSource, ProcName) as ( select [name], [path], (cast(cast([content] as varbinary(max)) as xml)).value( 'declare default element namespace " http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; (Report/DataSets/DataSet/Query/DataSourceName)[1]', 'varchar(50)') DataSource, (cast(cast([content] as varbinary(max)) as xml)).value( 'declare default element namespace " http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; (Report/DataSets/DataSet/Query/CommandText)[1]', 'varchar(50)') ProcName from ReportServer.dbo.Catalog ) select reports.[name], reports.[path], reports.DataSource, reports.ProcName from reports inner join your_db_name.sys.procedures procs on reports.ProcName = procs.name; In order to get as many records per report definition as ther are procedures/scripts it references, the guts of the xml\_content column must be first subjected to .nodes method which returns a collection of nodes. Once this is done, applying .value to each node of the collection will return desired results. The following query inserts relevant data to the temp table, much like in Fatherjack's blog. This appears to be a better alternative to a direct interrogation of the live Catalog table. Here is the script which works with SSRS 2005 (modify the xmlns value to the one specific to SSRS 2008 if needed) -- insert data into the temp table select [path], [name], convert(xml, convert(varchar(max),replace(convert( varbinary(max),[Content]),'', ''))) as xml_content into #ReportContents from ReportServer.dbo.Catalog where [Type] = 2; -- now query it: ;with xmlnamespaces( ' http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition' as ns) select rc.[name], rc.[path], R.item.value('(ns:DataSourceName)[1]', 'varchar(100)') DataSource, R.item.value('(ns:CommandText)[1]', 'varchar(100)') CommandText, R.item.value('(ns:CommandType)[1]', 'varchar(100)') CommandType from #ReportContents rc cross apply rc.xml_content.nodes( '//ns:Report/ns:DataSets/ns:DataSet/ns:Query') R(item) inner join your_db_name.sys.procedures procs on R.item.value('(ns:CommandText)[1]', 'varchar(100)') = procs.name order by 1; **End Edit** --> Oleg

You can add your code into a INLINE TABLE VALUED FUNCTION and use : select * from sys.procedures as p cross apply yourFunction(p.name)

What a coincidence, I wrote a blog article about this over the weekend - [ http://www.simple-talk.com/community/blogs/jonathanallen/][1] I hope it helps. [1]: http://www.simple-talk.com/community/blogs/jonathanallen/