INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Useful Stored Procedures

How to search all your objects for specific phrase/word by hmckillop
Posted: 3 Jun 04

Use the proc below and execute on your master database, then anytime you are looking for a specific word, variable, table, fieldname or whatever you need, you then can use this proc to find it.


CREATE PROC sp_search_code
(
@SearchStr     varchar(100),
@RowsReturned    int = NULL    OUT
)
AS
/*
To search your database code for the keyword 'unauthorized':
EXEC sp_search_code 'unauthorized'

To search your database code for the keyword 'FlowerOrders' and also find out the number of hits:
DECLARE @Hits int
EXEC sp_search_code 'FlowerOrders', @Hits OUT
SELECT 'Found ' + LTRIM(STR(@Hits)) + ' object(s) containing this keyword' AS Result
*************************************************************************************************/
BEGIN
    SET NOCOUNT ON

    SELECT    DISTINCT USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) AS 'Object name',
        CASE
             WHEN OBJECTPROPERTY(c.id, 'IsReplProc') = 1
                THEN 'Replication stored procedure'
             WHEN OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1
                THEN 'Extended stored procedure'                
            WHEN OBJECTPROPERTY(c.id, 'IsProcedure') = 1
                THEN 'Stored Procedure'
            WHEN OBJECTPROPERTY(c.id, 'IsTrigger') = 1
                THEN 'Trigger'
            WHEN OBJECTPROPERTY(c.id, 'IsTableFunction') = 1
                THEN 'Table-valued function'
            WHEN OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1
                THEN 'Scalar-valued function'
             WHEN OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1
                THEN 'Inline function'    
        END AS 'Object type',
        'EXEC sp_helptext ''' + USER_NAME(o.uid) + '.' + OBJECT_NAME(c.id) + '''' AS 'Run this command to see the object text'
    FROM    syscomments c
        INNER JOIN
        sysobjects o
        ON c.id = o.id
    WHERE    c.text LIKE '%' + @SearchStr + '%'    AND
        encrypted = 0                AND
        (
        OBJECTPROPERTY(c.id, 'IsReplProc') = 1        OR
        OBJECTPROPERTY(c.id, 'IsExtendedProc') = 1    OR
        OBJECTPROPERTY(c.id, 'IsProcedure') = 1        OR
        OBJECTPROPERTY(c.id, 'IsTrigger') = 1        OR
        OBJECTPROPERTY(c.id, 'IsTableFunction') = 1    OR
        OBJECTPROPERTY(c.id, 'IsScalarFunction') = 1    OR
        OBJECTPROPERTY(c.id, 'IsInlineFunction') = 1    
        )

    ORDER BY    'Object type', 'Object name'

    SET @RowsReturned = @@ROWCOUNT
END

Back to Microsoft SQL Server: Programming FAQ Index
Back to Microsoft SQL Server: Programming Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close