|
cornel
Starting Member
3 Posts |
 Posted - 2009-01-12 : 05:34:48
|
| Find given string in Reporting Services objects:How can I find all occurrences of a given string that is used in any SQL Server 2005 Reporting Services databases. (Essential, for example, when trying to identify where a given field name is used in Reporting Services)I have used the SQL below from within a stored procedure to find all occurrences of a string in a non-Reporting-Services database: -- Search for the given string in all Procedures, views and Paramenters (SELECT ROUTINE_NAME , SPECIFIC_Catalog as DatabaseName --- Database name that contains the object. , ROUTINE_TYPE As [Type] , DATA_Type , Character_maximum_length , LAST_ALTERED , CREATED , SQL_DATA_ACCESS --reads or modifies , COLLATION_Name , Character_set_name , ROUTINE_BODY FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE @stringToFind -- '%foobar%' ) UNION ALL ( Select SPECIFIC_NAME --parameter name , SPECIFIC_Catalog --- Database name that contains the object. , 'Parms' , DATA_TYPE , Character_maximum_length , NULL --LAST_ALTERED , NULL --Created , Parameter_mode --IN or OUT , COLLATION_Name , Character_set_name , NULL --ROUTINE_BODY from Information_Schema.parameters --where parameter_name like @stringToFind WHERE PARAMETER_NAME LIKE @stringToFind ) UNION ALL ( Select TABLE_NAME , TABLE_Catalog as DatabaseName --- Database name that contains the object. , 'View' , NULL -- ROUTINE_TYPE As [Type] , NULL --Character_maximum_length, , NULL --LAST_ALTERED , NULL --Created , Case is_Updatable WHEN 'NO' then 'Not Updateable' WHEN 'YES' then 'Is Updateable' ELSE is_Updatable end as SQL_DATA_ACCESS --reads or modifies , NULL -- COLLATION_Name , NULL --Character_set_name , NULL --ROUTINE_BODY from Information_Schema.views --where view_definition like @stringToFind WHERE VIEW_DEFINITION LIKE @stringToFind ) order by ROUTINE_NAME, DatabaseName, LAST_ALTERED descBest Regards,Cornel |
|
Find given string in Reporting Services objects