October 27, 2003 at 9:04 am
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
October 27, 2003 at 9:06 am
I think Brian Kelley did an article on this. Look at Resources -> Columnists -Brian Kelley.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 27, 2003 at 9:08 am
Found it
http://www.sqlservercentral.com/columnists/bkelley/procedurecache.asp
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
October 27, 2003 at 12:20 pm
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
October 28, 2003 at 7:00 am
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
October 28, 2003 at 8:17 am
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
October 28, 2003 at 8:21 am
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
October 29, 2003 at 3:37 pm
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.
October 29, 2003 at 4:11 pm
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