procedure naming prefixes

  • Hi there,

    Someone mentioned that having a prefix of 'sp_' in front of your stored procedure caused the system to look first in the master database and then the referenced database. Suggested to use 'usp_' instead, as sp_ might incur a performance hit for having to look twice.

    I've tried tracing thru called procs and haven't been able to notice any attempts to go to the master db, so can anyone confirm or deny this or point me some place that does?

    Trying to steamline code and if I can shave off some time by doing this then its worth doing. If its more of a visual niceity then I'll deal with them all being sp_

    Thanks,

    Chris

  • I think Brian Kelley did an article on this. Look at Resources -> Columnists -Brian Kelley.

    Frank

    http://www.insidesql.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Found it

    http://www.sqlservercentral.com/columnists/bkelley/procedurecache.asp

    Frank

    http://www.insidesql.de

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • awesome article. Thanks!

    Chris

    edit: Of course now... how can I go thru and quickly rename 450+ procs 🙁

    Learning is painful.

    Edited by - ctklein on 10/27/2003 2:31:23 PM

  • Look at sp_rename. Based on your original question, I assume that your procs are not in master. So, you might write a script that finds everything in sysobjects with name like 'sp_%' and type (or xtype) = 'p', then use sp_rename to change the prefix.

    Steve Phelps

    SQL Server DBA

    American Fidelity Group

  • Chris,

    Paste this into QA (Query Analyzer) and run it against your database. It will return a list of EXEC commands. Copy the EXEC commands into QA and run them. It will rename all SPs that start with sp_ to start with proc_. You can change the proc_ as desired.

    SELECT 'EXEC(''sp_rename @objname = '''''+LTRIM(u.name)+'.'+LTRIM(s.name)+''''''+', @newname = '''''+LTRIM(u.name)+'.proc_'+LTRIM(RIGHT(s.name,4 ))+''''''+''')'

    FROM sysobjects s, sysusers u

    WHERE s.uid = u.uid

    AND s.name LIKE 'sp%'

    AND xtype = 'P'

    ORDER BY s.name

    Troy Tabor

    Web Applications Project Manger

    Choice Solutions LLC

     
     
  • Whoops... I messed up the RIGHT function. Use the following code instead.

    SELECT 'EXEC(''sp_rename @objname = '''''+LTRIM(u.name)+'.'+LTRIM(s.name)+''''''+', @newname = '''''+LTRIM(u.name)+'.proc_'+LTRIM(RIGHT(s.name,LEN(s.name)-3))+''''''+''')'

    FROM sysobjects s, sysusers u

    WHERE s.uid = u.uid

    AND s.name LIKE 'sp%'

    AND xtype = 'P'

    ORDER BY s.name

    Troy Tabor

    Web Applications Project Manger

    Choice Solutions LLC

  • Here is a SP that I use quite a bit. Please use it with caution and make sure to double check the output text to verify that it's correct.

    The problem with simply using sp_rename is that you will end up with calls inside sp's that don't have the new name.

     
    
    IF EXISTS(SELECT * FROM sysobjects WHERE id = object_id('ReplaceWordInObjectScript'))
    DROP PROCEDURE ReplaceWordInObjectScript
    GO
    CREATE PROCEDURE ReplaceWordInObjectScript
    (
    @SourceStr varchar(255)
    , @DestStr varchar(255)
    , @ObjectType varchar(2) = 'P'
    )
    AS
    BEGIN

    -- Sample Call
    -- ReplaceWordInObjectScript 'WordToFind', 'NewWord'[, 'TF']

    DECLARE @ObjectName sysname
    , @ObjectID int
    , @Comment varchar(8000)
    , @cName cursor
    , @cText cursor
    , @NameStatus int
    , @TextStatus int


    -- PRINT '-----------------------------------------------------------------------------------------------------------------------'
    -- PRINT '-- Save the results to a file and then execute the file to replace the text'
    -- PRINT '-----------------------------------------------------------------------------------------------------------------------'


    SET @cName = cursor FOR
    SELECT o.name, o.id
    FROM sysobjects o
    JOIN syscomments c
    ON o.id = c.id
    WHERE c.text LIKE '%' + @SourceStr + '%'
    AND o.type IN ('S','FN','TR','P','IF','F','V','TF')--= @ObjectType--!= 'U'




    AND name <> 'ReplaceWordInObjectScript'
    FOR READ ONLY

    OPEN @cName

    FETCH NEXT FROM @cName INTO @ObjectName, @ObjectID
    SET @NameStatus = @@Fetch_Status
    WHILE @NameStatus = 0
    BEGIN
    -- PRINT '-----------------------------------------------------------------------------------------------------------------------'
    -- PRINT '-- Modifying ' + @ObjectName
    -- PRINT '-- By Replacing "' + @SourceStr + '" with "' + @DestStr + '"'
    -- PRINT '-----------------------------------------------------------------------------------------------------------------------'

    SET @cText = CURSOR FOR
    SELECT c.text
    FROM syscomments c
    WHERE c.id = @ObjectID
    ORDER BY colid
    FOR READ ONLY

    OPEN @cText
    FETCH @cText INTO @Comment
    SET @TextStatus = @@FETCH_STATUS
    WHILE @TextStatus = 0
    BEGIN
    -- Note, Since there is no easy way of telling how much space is between Create and
    -- the object type I use the replace multiple times.
    SELECT @Comment = REPLACE(@Comment, @SourceStr, @DestStr)
    SELECT @Comment = REPLACE(@Comment,'CREATE TRIGGER' , 'ALTER TRIGGER')
    SELECT @Comment = REPLACE(@Comment,'CREATE TRIGGER' , 'ALTER TRIGGER')
    SELECT @Comment = REPLACE(@Comment,'CREATE TRIGGER' , 'ALTER TRIGGER')
    SELECT @Comment = REPLACE(@Comment,'CREATE TRIGGER' , 'ALTER TRIGGER')
    SELECT @Comment = REPLACE(@Comment,'CREATE PROC' , 'ALTER PROC')
    SELECT @Comment = REPLACE(@Comment,'CREATE PROC' , 'ALTER PROC')
    SELECT @Comment = REPLACE(@Comment,'CREATE PROC' , 'ALTER PROC')
    SELECT @Comment = REPLACE(@Comment,'CREATE PROC' , 'ALTER PROC')
    SELECT @Comment = REPLACE(@Comment,'CREATE PROC' , 'ALTER PROC')
    SELECT @Comment = REPLACE(@Comment,'CREATE FUNCTION' , 'ALTER FUNCTION')
    SELECT @Comment = REPLACE(@Comment,'CREATE FUNCTION' , 'ALTER FUNCTION')
    SELECT @Comment = REPLACE(@Comment,'CREATE FUNCTION' , 'ALTER FUNCTION')
    SELECT @Comment = REPLACE(@Comment,'CREATE FUNCTION' , 'ALTER FUNCTION')
    SELECT @Comment = REPLACE(@Comment,'CREATE FUNCTION' , 'ALTER FUNCTION')
    SELECT @Comment = REPLACE(@Comment,'CREATE VIEW' , 'ALTER VIEW')
    SELECT @Comment = REPLACE(@Comment,'CREATE VIEW' , 'ALTER VIEW')
    SELECT @Comment = REPLACE(@Comment,'CREATE VIEW' , 'ALTER VIEW')
    SELECT @Comment = REPLACE(@Comment,'CREATE VIEW' , 'ALTER VIEW')
    SELECT @Comment = REPLACE(@Comment,'CREATE VIEW' , 'ALTER VIEW')

    PRINT @Comment
    FETCH @cText INTO @Comment
    SET @TextStatus = @@FETCH_STATUS
    END
    PRINT 'GO'
    -- PRINT '-----------------------------------------------------------------------------------------------------------------------'
    -- PRINT '-- FINISHED Modifying ' + @ObjectName
    -- PRINT '-- By Replacing "' + @SourceStr + '" with "' + @DestStr + '"'
    -- PRINT '-----------------------------------------------------------------------------------------------------------------------'
    PRINT ''
    PRINT ''
    PRINT ''
    CLOSE @cText

    FETCH NEXT FROM @cName INTO @ObjectName, @ObjectID
    SET @NameStatus = @@Fetch_Status

    END

    CLOSE @cName
    DEALLOCATE @cName
    DEALLOCATE @cText

    END -- Procedure


    Gary Johnson

    Microsoft Natural Language Group

    DBA, Sr. DB Engineer




    Gary Johnson
    Microsoft Natural Language Group
    DBA, Sr. DB Engineer

    This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

  • quote:


    awesome article. Thanks!

    Chris

    edit: Of course now... how can I go thru and quickly rename 450+ procs 🙁

    Learning is painful.


    http://www.moshannon.com/speedferret.html

    --Jonathan



    --Jonathan

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply