Find all Databases with AutoShrink Turned On

As far as I can tell, the only thing that all SQL Server DBAs agree on is that AutoShrink should be turned off. You never even see anyone say, "It depends." This level of agreement is, I think, unique in all of SQL Server-land. I mean, you would think we'd all agree that clustering on a non-sequential GUID is a Very Bad Idea, but there's actually people who argue in favor of it. Go figure.

So anyway, the script below will find all the databases (SQL Server 2000 and up) that for some mysterious reason have it turned on. (Of course, we all know the reason is that some yo-yo doesn't understand that big, empty databases run faster than small, full databases, but that's another story...) If you run this script on CMS, it will work across all the servers in the group (that's why we cursor through the resultsets instead of just returning them - it makes the output in CMS much easier to read).

-- Find all databases with AutoShrink turned on. 
declare @DatabaseName sysname

if left(convert(nvarchar, serverproperty('productversion')), 1) <> '8'
begin
    declare cur cursor local fast_forward for
        select name   as 'Database Name'
          from sys.databases
         where is_auto_shrink_on = 1
           and state_desc = 'ONLINE'
           and database_id > 4

    open cur
    fetch next from cur into @DatabaseName

    while @@fetch_status = 0
    begin
        print @DatabaseName
        fetch next from cur into @DatabaseName
    end

    close cur
    deallocate cur
end
else
begin
    declare cur cursor local fast_forward for
        select name   as 'Database Name'
          from master..sysdatabases
         where status & 4194304 = 4194304
           and status & 512 = 0
           and dbid > 4

    open cur
    fetch next from cur into @DatabaseName

    while @@fetch_status = 0
    begin
        print @DatabaseName
        fetch next from cur into @DatabaseName
    end

    close cur
    deallocate cur
end

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