Jan
29

Find Instances of String in DB Objects

I have a folder full of SQL utility scripts which I’ve compiled over the years.  One of which would search all stored procedures within a given database for instances of a specified string.  This type of script is really helpful if you need to find all routines which reference a specific table column.  This morning I extended the routine to include views and user-defined functions as well.  I also made a change to include all databases on the SQL Server except of the four system databases.

Declare @SearchString varchar(50) Select @SearchString = ‘SearchText‘ – TDB Create Table #Results ( [Database] varchar(255), [Object] varchar(255), [Type] char(1) ) SET NOCOUNT ON Declare @DBName sysname; Set @DBName = ” Declare @Qry nvarchar(4000); Set @Qry = ” WHILE @DBName IS NOT NULL BEGIN SET @DBName = ( SELECT MIN(name) FROM master..sysdatabases WHERE name NOT IN (‘master‘, ‘model‘, ‘tempdb‘, ‘msdb‘) AND DATABASEPROPERTY(name, ‘IsOffline‘) = 0 AND DATABASEPROPERTY(name, ‘IsSuspect‘) = 0 AND name > @DBName ) IF @DBName IS NULL BREAK SET @Qry = ‘SELECT ”’ + @DBName + ”’, O.Name, O.Type FROM ‘ + QUOTENAME(@DBName) + ‘..SysObjects O JOIN ‘ + QUOTENAME(@DBName) + ‘..SysComments C ON O.ID = C.ID WHERE O.Type in (”P”,”U”,”V”) AND PATINDEX(”%‘ + @SearchString + ‘%”,C.Text) > 0 ORDER BY O.Type, O.Name‘ INSERT INTO #Results EXEC (@Qry) END Select Distinct [Database],[Object],[Type] From #Results Order By [Database], [Object] Drop Table #Results

Notice the script used PATINDEX().  What does it do?  It returns the starting position of the first occurrence of a pattern in a specific expression, or zero if the pattern is not found, on all valid text and character data types.  You may have noticed the expression sought by PATINDEX() must be enclosed in “%” signs. but within the expression we could use wildcards “%” for any number of letters, “_” for a single character, [] to specify a range of characters and [^] to skip a range, just as you do when using LIKE comparisons.

Posted by Ben Griswold Samples, TSQL Subscribe to RSS feed

5 Comments to “Find Instances of String in DB Objects”

  1. SqlServerForum.org » Blog Archive » Find Instances of String in Database Objects says:
    February 6, 2007 at 3:37 pm

    [...] Original post by JohnnyCoder and software by Elliott Back [...]

  2. marketing in miami says:
    November 11, 2011 at 11:17 pm

    Hi! Do you know if they make any plugins to assist with SEO? I’m trying to get my blog to rank for some targeted keywords but I’m not seeing very good gains. If you know of any please share. Many thanks!

  3. Harry Scierka says:
    November 15, 2011 at 8:59 pm

    I simply couldn’t depart your site before suggesting that I extremely enjoyed the usual info a person supply to your guests? Is going to be back frequently to check out new posts

  4. zaproszenia ślubne piotrków trybunalski says:
    November 18, 2011 at 4:17 am

    Hey very nice site!! Man .. Excellent .. Amazing .. I will bookmark your site and take the feeds also…I am happy to find a lot of useful info here in the post, we need develop more techniques in this regard, thanks for sharing. . . . . .

  5. Lenny Leopard says:
    January 16, 2012 at 9:35 am

    Good post. It’s really a first-rate article. I noticed your entire vital points. Many thanks.

Leave a Reply

You can wrap your code with [ruby][/ruby] or [python][/python] blocks for syntax highlighting and you can use these traditional tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>