November 27, 2003 at 5:34 am
Hello
I am looking for a script that will search though the text of all sp's & replace a string [a production Linked Server Name] with another [Test Linked server name :)]
I think I have a method using a cursor & sp_helptext sp_'name', but don't want to reinvent the wheel.
Anybody done this before?
Jacko
November 27, 2003 at 5:57 am
You can query against syscomments to find matches (search against text and pull the id), but then you're still going to have to do something like a cursor with sp_helptext and alter proc or something along those lines.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
November 27, 2003 at 7:07 am
Thanks for that, I'll have a go at knocking one up. If I succeed, (and succeed I must 🙂 ) , I'll post it here.
Jacko
November 28, 2003 at 2:08 am
Couldn't you script them out and find and replace in QA?
November 28, 2003 at 5:21 am
I use the following code for finding strings in sps:
SELECT DISTINCT a.name AS SPName
FROM syscomments b, sysobjects a
WHERE b.text LIKE '%authors%'
AND a.id=b.id
AND a.type='P'
Would the following work (not tried it!):
SELECT Replace(b.text, 'TestServer', 'ProductionServer')
FROM syscomments b, sysobjects a
WHERE a.id = b.id
AND a.type = 'P'
November 28, 2003 at 7:47 am
OK, using the suggestions above, this is what I have got so far:
Declare @tmpTable table (myID int NOT NULL IDENTITY (1, 1),
Query nvarchar(4000) NOT NULL)
Declare @step int, @i int
Declare @q nvarchar(4000)
Insert Into @tmpTable
Select b.text
From syscomments b, sysobjects a
Where a.id = b.id
AND a.type = 'P'
AND b.text LIKE '%LinkedSQL1%'
AND a.name not like 'dt%'
set @i = (Select max(myID) from @tmpTable)
set @step = 0
While @step < @i
Begin
Set @step = @step + 1
set @q = (Select query from @tmpTable where myId = @step)
print len(@q)
set @q = Replace(Replace(@q, '[LinkedSQL1].',''),'Create','Alter')
print len(@q)
print @q
-- execute sp_executesql @q
End
as turns out, the SP in question is over 4000 bytes (!) so this won't do it. Still, the techniques above may be of some use to someone.
b4 you ask, I didn't write the sp... 🙂
Edited by - jacko999 on 11/28/2003 09:31:35 AM
November 28, 2003 at 12:17 pm
You can always write to a temporary file (xp_cmdshell using echo >>), then use xp_cmdshell to call isql or osql and login/execute the create proc statement.
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
November 30, 2003 at 2:16 am
Must this be accomplished in TSQL only? If you are amenable to using a procedural language like VB, this is a lot easier using SQLDMO (Documented in Books Online). It's pretty much like:
For each sp in StoredProcedures
sp.text = replace(sp.text,oldtext,newtext)
Next sp
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply