5/04/2012

Helpful SQL Search Queries

Here's a few queries I run often:

1. This query will search through all Stored Procedures on a SQL-Server Database and return the Sproc name and code.  Very helpful to doing global searches on a particular string/field.



SELECT ROUTINE_NAME, ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%TEXT_TO_SEARCH_FOR%'
AND ROUTINE_TYPE='PROCEDURE'

2. This query will search through all Reports stored in SQL-Server Reporting Services for particular Keyword.  Very useful!



USE [ReportServer]
SELECT
        CONVERT(varchar(MAX)
      , CONVERT(varbinary(MAX), c.[content]))
      , c.[Path]
      , c.[Name]
FROM [ReportServer].[dbo].[catalog] c
WHERE path not like '/Data Sources/%'
AND c.[content] IS NOT NULL
AND CONVERT(varchar(MAX), CONVERT(varbinary(MAX), c.[content]))
LIKE '%TEXT_TO_SEARCH_FOR%'



No comments:

Post a Comment

Note: Only a member of this blog may post a comment.