Find all Occurrences of Text in all Stored Procedures in all Databases

Sometimes you'd like to find all occurrences of a piece of text in every stored procedure, trigger, etc., possibly to replace it with a new value. The script below will do just that; it returns the database name, the object name, and the object type. Note that it works for both SQL Server 2000 and post-SQL Server 2000 versions.

One thing to note is that the @TextToFind uses the LIKE operator with its ESCAPE argument set to the dollar sign symbol. I added the ESCAPE argument just to make it easier to search for text such containing the underscore character, such as 'My_HardToFind_Thing". Of course, if you're searching for text with a dollar sign symbol in it, you'll want to change the ESCAPE argument to something else.

(The script below can also be run in CMS, which will allow you to also return results across server groups.)

-- Simply set the text you want to look for here. 
declare @TextToFind nvarchar(4000)
set @TextToFind = 'THIS IS MY TEXT TO FIND'

declare @Sql nvarchar(4000)

set @Sql =
    'use [?]

     declare @DatabaseName sysname
     declare @ProcName     sysname
     declare @ProcType     nvarchar(60)

     if left(convert(nvarchar, serverproperty(''productversion'')), 1) <> ''8''
     begin
         declare cur cursor local fast_forward for
            select distinct ''?''          as ''Database Name''
                          , o.name         as ''Object Name''
                          , o.type_desc    as ''Object Type''
              from sys.sql_modules   m
              join sys.objects       o
                on m.object_id = o.object_id
             where m.definition like ''%' + @TextToFind + '%'' escape ''$''

        open cur
        fetch next from cur into @DatabaseName, @ProcName, @ProcType

        while @@fetch_status = 0
        begin
            print @DatabaseName + '' '' + @ProcName + '' '' + @ProcType
            fetch next from cur into @DatabaseName, @ProcName, @ProcType
        end

        close cur
        deallocate cur
    end
    else
    begin
         declare cur cursor local fast_forward for
            select distinct ''?''               as ''Database Name''
                          , object_name(c.id)   as ''Object Name''
                          , o.xtype             as ''Object Type''
              from syscomments   c
              join sysobjects    o
                on c.id = o.id
             where c.[text] like ''%' + @TextToFind + '%'' escape ''$''
               and objectproperty(c.id, ''isprocedure'') = 1

        open cur
        fetch next from cur into @DatabaseName, @ProcName, @ProcType

        while @@fetch_status = 0
        begin
            print @DatabaseName + '' '' + @ProcName + '' '' + @ProcType
            fetch next from cur into @DatabaseName, @ProcName, @ProcType
        end

        close cur
        deallocate cur
    end
    
    '

exec sp_msforeachdb @Sql

Find and Kill All System-Named Statistics

I hate system-named objects.

Some of this is to be expected from someone like me, who, depending on the decade, could be called "persnickety", "perfectionist", "OCD", or "The Anal Retentive Chef." But there's also a very practical reason to name all your database objects.

If you're using a third-party tool to synch up database schemas, you may find that every time you copy a system-named object from one side of the synch to the other, an entirely new name is generated. For example, you might have a statistic object named dbo.Customers._WA_Sys_00000001_45544755 on the left-hand side of the synch, but when you tell the tool to copy it to the right-hand side, you get a statistic named dbo.Customers._WA_Sys_00000001_436BFEE3.

Well, that won't do.

Below is a stored procedure that locates and drops all system named statistics. After you run this, just create a new statistic object with a name that follows your SQL nomenclature guidelines. (Whaddaya mean you don't have any? Get some!)

-- Comment. 
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO


-------------------------------------------------------------------------------
-- Drops all system-named statistics.                                          
-------------------------------------------------------------------------------

CREATE PROCEDURE dbo.usp_CleanupStatisticsWithSysNames
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @Cmd        NVARCHAR(MAX) = ''
    DECLARE @SchemaName sysname       = ''
    DECLARE @TableName  sysname       = ''
    DECLARE @StatName   sysname       = ''

    DECLARE cur CURSOR FOR
        SELECT m.name   AS SchemaName
             , t.name   AS TableName
             , s.name   AS StatName
          FROM sys.stats           s
          JOIN sys.stats_columns   sc
            ON s.object_id = sc.object_id
           AND s.stats_id  = sc.stats_id
          JOIN sys.tables          t
            ON s.object_id = t.object_id
          JOIN sys.schemas         m
            ON t.schema_id = m.schema_id
         WHERE (s.name LIKE '\_WA\_Sys\_%' ESCAPE '\'  OR  s.auto_created = 1)
           AND s.object_id > 100
      ORDER BY SchemaName
             , TableName
             , StatName

    OPEN cur
    FETCH NEXT FROM cur INTO @SchemaName, @TableName, @StatName

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Cmd = 'DROP STATISTICS ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + '.' + QUOTENAME(@StatName)
        RAISERROR(@Cmd, 10, 1) WITH NOWAIT
        EXEC sp_executesql @Cmd
        FETCH NEXT FROM cur INTO @SchemaName, @TableName, @StatName
    END

    CLOSE cur
    DEALLOCATE cur
END

Rumor has it that the "WA" in the system-generated names stands for "Washington", as in "Redmond".