March 28, 2011 at 9:07 am
As more or less already said, however technically a 'smart' solution, the fact that this is the chosen way reveals that sourcecontrol and auditing of changes is hardly done in the company this was applied.
If you do proper source control with sql scripts in SSMS or Visual Studio projects and using VSS or TFS, there are much better ways to do a find and replace in all scripts and deploy the changed stored procedures in a controlled manner.
March 28, 2011 at 9:09 am
lancebeacroft (3/28/2011)
I can't think of a situation where I would use the posters solution. It feels somewhat 'cowboy' and slap dash for my liking.L
(Also @GrassHopper )
I agree, but consider the case where you're managing database pairs, let's call them "OLTP" and "OLAP", and you have views on the OLAP database:-
OLAP.vw_referenceData1 -> OLTP.referenceData1
OLAP.vw_referenceData2 -> OLTP.referenceData2
.
.
Now, if you want to create a copy of these databases for QAing, UAT, Demos, Training and so on, you have to be able to bulk-change all the views, stored procedures and functions to change the main database.
Yes. This is horrible. No, I would never advise anyone do this, because it's not optimal. Yes, it's a maintenance nightmare. But some people have situations such that this.
So, what you need in this case is something similar to what the OP suggests, so that you can quickly re-generate all the structure without having to deploy your full DB source, and then 'pour in' all the extra data.
Let me re-iterate. I *DO NOT* condone this, I just have to do it, cos it's a system I took over from others. So while the OP's solution isn't fantastic, there is a niche out there for mass-changes such as this. My only criticism of it is that it might not go far enough for it to be useful to a lot of people..
Here's our version (Horrible as it is!). Don't reply with the crapness of the code. I know. But it's used several times a day every day, so I know it works.
/****** Object: StoredProcedure [dbo].[dba_MassRename] Script Date: 02/10/2009 00:57:04 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dba_MassRename]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[dba_MassRename]
GO
CREATE PROCEDURE dba_MassRename
@TextFrom varchar(50),
@TextTo varchar(50)
AS
BEGIN
SET NOCOUNT ON;
-- loop through three times. First time, Views, second time SPS, third time functions.
DECLARE @actions TABLE
(
sysobjtype1 varchar(10),
sysobjtype2 varchar(10),
wordtochange varchar(20)
)
INSERT INTO @actions (sysobjtype1,sysobjtype2,wordtochange)
SELECT 'V','V','VIEW'
UNION
SELECT 'P','P','PROC'
UNION
SELECT 'TF','FN','FUNC'
DECLARE @objnames TABLE
(
Objname varchar(255)
)
DECLARE @t1 varchar(10)
DECLARE @t2 varchar(10)
DECLARE @wtc varchar(10)
DECLARE @objname varchar(255)
DECLARE @objtext nvarchar(max)
DECLARE @objtexttmp nvarchar(max)
DECLARE @CT int
DECLARE @modct int
SELECT @CT = count(*) from @actions
WHILE @CT > 0
BEGIN
SELECT TOP(1) @t1 = sysobjtype1, @t2 = sysobjtype2, @wtc = wordtochange
FROM @actions
print 'PROCESSING ' + @wtc + 'S
'
SET @modct = 0
DELETE @objnames
INSERT INTO @objnames (objname)
SELECT DISTINCT NAME FROM sys.objects where type = @t1 or type = @t2
DECLARE OBJCURSOR CURSOR FOR
SELECT objname from @objnames
OPEN OBJCURSOR
FETCH NEXT FROM OBJCURSOR INTO @objname
WHILE @@FETCH_STATUS = 0
BEGIN
-- Get the text of the thing we want to work on.
SELECT @objtext = object_definition(object_id(@objname))
if @objtext is not null
BEGIN
-- change any target DB back, just in case. (prevents the XXXXXUATUAT case
set @objtext = REPLACE(@objtext,@TextTo,@TextFrom)
if charindex(@TextFrom,@objtext,1) > 1
BEGIN
SET @modct = @modct + 1
-- GET RID OF SPARE SPACES
SET @objtexttmp = ''
WHILE @objtexttmp <> @objtext
BEGIN
SET @objtexttmp = @objtext
SET @objtext = REPLACE(@objtexttmp,'CREATE ','CREATE ')
END
-- Change CREATE FUNC to ALTER FUNC
set @objtext = REPLACE(@objtext,'CREATE '+@wtc,'ALTER '+@wtc)
-- Change the SRC DB to tgt DB
set @objtext = REPLACE(@objtext,@TextFrom,@TextTo)
-- re-execute the item
BEGIN TRY
exec sp_executeSQL @objtext
END TRY
BEGIN CATCH
print 'Problem processing ' + @wtc + ' ' + @objname
END CATCH
END
END
FETCH NEXT FROM OBJCURSOR INTO @objname
END
CLOSE OBJCURSOR
DEALLOCATE OBJCURSOR
print LTRIM(RTRIM(STR(@modct))) + ' object(s) processed
'
SELECT TOP(1)@t1 = sysobjtype1,
@t2 = sysobjtype2,
@wtc = wordtochange
FROM @actions
DELETE FROM @actions where wordtochange = @wtc
SELECT @CT = count(*) from @actions
END
SELECT name as [Unprocessed Objects] from sysobjects so
inner join syscomments sc on so.id = sc.id
where sc.text like '%'+@TextFrom+'%'
and sc.text not like '%'+@TextTo+'%'
SELECT name as [Processed Objects] from sysobjects so
inner join syscomments sc on so.id = sc.id
where sc.text like '%'+@TextTo+'%'
and sc.text not like '%'+@TextFrom+'%'
END
<warning type="A$$Covering"> If you want to play with this, please do.. If you can improve it, I'd love to make it better.. but if you're a SQL novice, please don't use this blindly. Make sure you know what you're doing, and above-all, test it on a copy first 🙂 </warning>
N.
March 28, 2011 at 9:59 am
lancebeacroft (3/28/2011)
Ahh, now we are in a different scenario. What or who would stop you adopting best practice?
Time (yes, it's a time savings to do it right but no time right now to set it up.) Money (can't afford a source control system or the systems to put it up on ). Tradition (no one wants to change how it's been done because it works.)
No, none of these are great arguments but you deal with the situation you're in. Not the ideal one.
March 28, 2011 at 1:13 pm
Interesting article and all. But I hope I'm never in a situation to need this. I've always had employers and clients that have stored proc scripts (plus many other) under source control. There's something to be said for process, even when it slows us down.
March 28, 2011 at 1:20 pm
sada235 (3/28/2011)
There's something to be said for process, even when it slows us down.
Amen to that.
March 28, 2011 at 3:09 pm
I would use sys.sql_modules or INFORMATION_SCHEMA.routines. However, more importantly than that, I would convert the @@IDENTITY to the output parameter method suggested by Microsoft since there is a bug related to identity:
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=328811
Other than that, I would always execute code that executes alters on the procedures for change tracking history in the metadata.
Nice thought provoking article.
March 28, 2011 at 6:02 pm
deroby (3/28/2011)
I sincerely think changing a cursor into a WHILE loop to be a bad idea. The while-loop approach (sometimes called 'a poor man's cursor') hardly ever brings benefits but potentially can cause the performance to drop noticeable. It all depends a bit on the amount of data to be fetched, as well as the layout and search-ability of said data.
Ah... someone who takes after my own heart. I've never seen a decent While Loop drop performance compared to a Cursor before. Other than that, I absolutely agree... especially since the "guts" of Cursor usage are (drum roll please)... While Loops. 🙂
One company I worked for had a consultant come in prior to my being hired. They had some serious performance problems and an average of 640 deadlocks per day with spikes to 4000, to boot. The consultant told them that changing all of their Cursors to Temp Tables and While loops would really go a long way in solving their problems. Of course, after paying that consultant a ridiculous amount of money and spending a quarter million dollars in man power and repairing a bunch of code their misguided efforts screwed up, it made no difference at all... especially when their loops continued to call more than 30 different stored procedures up to 8 levels deep that were all operating on a RBAR basis. Simply changing the Cursors to Forward Only Read Only Static (think system-provided Temp Table) would have been just as effective and a whole lot easier and cheaper to do. It still wouldn't have helped the deadlocks, though... they had a single 3rd-party-provied stored proc which updated a (ugh!) sequence table. Repairing that dropped the deadlocks to almost zero deadlocks that day and every day. The rest came from other, ummm.... less than optimal code.
Don't get me wrong... I'm still a bit of an anti-Cursor zealot as I am with any form of RBAR or Hidden RBAR... but changing Cursors to Temp Tables and While loops just isn't something I'd spend any time on. And, yes, there are some places where Cursors and While Loops are justified but not in the places a lot of folks think.
Damn... that turned into a bit of a rant, didn't it? :hehe:
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2011 at 6:21 pm
Although I don't necessarily agree with the methods you used, I have to agree with what has already been said... this is the general direction DBA's should think in when faced with such large problems. It also pulled out some other good suggestions in the discussion. Nicely done.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2011 at 10:59 pm
Thanks for taking the time to post your article and solution for this topic.
Though most wouldn't want to use a cursor or RBAR script in the database, how many use sp_msforeachtable or sp_msforeachdb? I would prefer set-based solutions, but this is an administrative type script that won't be run millions of times an hour. Also, it should only be run by admins on an infrequent basis on a dev server to generate the scripts that would be promoted to the QA and then beta and then finally Production servers. When done in such a fashion the RBAR script would never touch the production server.
Thanks again for the article.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 4, 2011 at 12:00 pm
Sorry for the delay. I've been out of town all last week.
Yes, this will handle even very large stored procedures. The only thing you need to watch for is that you don't exceed the buffer size of the message window. Off hand I don't know what that is though.
Thanks
May 2, 2012 at 3:16 pm
I'm not the most experienced with the intricacies of SQL Server, but replacing one bit of text with another bit of text is something I do a LOT of. I don't need a fancy SQL script to do it either...
1: Right click the database
2: Click Tasks
3: Click Generate Scripts
4: Select the items you would like to modify (All SPROCs?)
5: Use Find and replace (plain old text editor...) to replace CREATE PROCEDURE with ALTER PROCEDURE
6: Find and replace the thing you want... IE, @@Identity with Whatever
No cursors, no whiles, no lingering extra code... straight and simple.
What's wrong with this approach? 🙂
May 2, 2012 at 3:24 pm
[Responding to: kevonh]
Nothing wrong with your approach, specifically. But this thread is about an article posted here:
http://www.sqlservercentral.com/articles/Stored+Procedures/72623/
And in the first paragraph, the article's author states that s/he is trying to solve a problem across multiple servers and databases. Your approach really only would be good for a single database.
Regards.
May 20, 2012 at 5:25 am
Despite what's been said I quite like this.
It's versatile and can be used to change any element of the script (i.e. table references) and if you have to edit several hundred procedures across numerous db's then it's a massive time saver. Sometimes an approach like this simply is the best way. Source control is great in an ideal world but not so useful when you're being told 'I need this to happen yesterday'.
And besides... any mistake made can easily be converted back using the same method!
One small addition - Put something in the naming convention that will stop the 'Get' and 'Print' procedures from appearing as Alter scripts themselves
By adding:
AND LEFT(name,3) != 'dba'
to the first where clause in the 'Get' procedure I was able to prevent this from happening
May 21, 2012 at 8:02 am
Thanks carmstrong592. That's exactly what I was going for.
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply