January 31, 2011 at 9:44 am
Hello, everyone.
I have been asked by my IT Development department to perform a difficult task on my client's databases.
Our application uses several stored procedures, to wich I have access to the definition on the central server, that mention database and table names in a fixed way.
Now, we are implementing table alias for these changing tables, as to change their database location from one to the other without impacting the application.
So, my problem is as follows: all my stored procedures mention these tables in the format MyDB.table and I need to change them to [MyDB.table], wich is the T-SQL synonym format we will be using for each of them.
I can already with a CTE replace most of the entries inside the SPs, but some of them have line feeds and other characters that fail the replace routine.
My code is this:
with cte (Name,SP,OldField,NewField) as (
select
Nome =a.name,
SP =b.definition,
OldField=SUBSTRING(
b.definition,
patindex('%MyDB.dbo.%',b.definition),
patindex('%MyDB.dbo.%',b.definition) + patindex('% %',(substring(b.definition,patindex('%MyDB.dbo.%',b.definition),len(b.definition)))) - patindex('%MyDB.dbo.%',b.definition)-1
),
NewField= '[' +
SUBSTRING(
b.definition,
patindex('%MyDB.dbo.%',b.definition),
patindex('%MyDB.dbo.%',b.definition) + patindex('% %',(substring(b.definition,patindex('%MyDB.dbo.%',b.definition),len(b.definition)))) - patindex('%MyDB.dbo.%',b.definition)-1
)
+ ']'
from sys.procedures as a
inner join sys.sql_modules as b on a.object_id=b.object_id
where
b.definition like '% MyDB.dbo.%'
and a.is_ms_shipped=0
)
select
Name = cte.Nome,
NewSP = replace(cte.SP,cte.OldField,cte.NewField)
from cte
I apologise for the poorly formatted code, I am quite the posting-newb 😛
I can already replace several entries in some SPs, so I will assume that all recursion is working and that is not the problem.
However there is one SP that has those line changes and fails the replacement.
Any ideas....?
Thanks in advance,
Ivo Pereira
IT Consultant
Portugal
January 31, 2011 at 9:56 am
if it fails on only one procedure, i'd just fix it manually and be done.
Are you sure this is something that must run in all situations, or is this just a one time fix?
if your code fixed all but one, I'd find the reason for academic reasons i guess, but I'd peek deeper at your code example now.
why not just do a find and replace to your local copies, and issue a complete set of ALTER PROC functions instead?
Lowell
January 31, 2011 at 10:21 am
Hello, Lowell.
Thanks for the quick reply. Unfortunately, I have several SPs written using line feeds in these specific table names, according to the sample I give below:
DELETE FROM MyDB.dbo.labs
FROM OtherDB.dbo.labs AS xx
RIGHT OUTER JOIN MyDB.dbo.labs AS yy ON xx.labID=yy.labID
WHERE (xx.labID IS NULL)
As you can see, there is a new line after the table name I want to replace (MyDB.dbo.labs) and, as such, my replace code fails.
I tried to demonstrate this using this snippet (bad formatting again.. sorry :S )
declare
@string nvarchar(256)= '
DELETE FROM MyDB.dbo.labs
FROM OtherDB.dbo.labs AS xx
RIGHT OUTER JOIN MyDB.dbo.labs AS yy ON xx.labID=yy.labID
WHERE (xx.labID IS NULL)
' ,
@searchstring nvarchar(256) = 'MyDB.dbo.'
select
OldField =SUBSTRING(
@string,
patindex(@searchstring,@string),
patindex(@searchstring,@string) + patindex('% %',(substring(@string,patindex(@searchstring,@string),len(@string)))) - patindex(@searchstring,@string)-1
),
NewField = '[' +
SUBSTRING(
@string,
patindex(@searchstring,@string),
patindex(@searchstring,@string) + patindex('% %',(substring(@string,patindex(@searchstring,@string),len(@string)))) - patindex(@searchstring,@string)-1
)
+ ']'
where
@string like '%'+@searchstring+'%'
So I am stuck with several of these SPs with the line feeds. I should have explained before that my search criteria is finding the next SPACE character after my search string (that's why I use the patindex(% %') thing above).
And since these lines do not have a SPACE after the table name, I can´t replace them..
Any ideas?
Thanks for the help,
Ivo Pereira
IT Consultant
Portugal
January 31, 2011 at 11:09 am
I'm asking you to think outside of the box for a moment. you seem to be locked into one method, and i'm suggesting an alternative.
it looks to me like you want to send a sql statement to your client which will update the stored procedures to have some new definitions...
but instead of sending the actual stored procedure definitions, you are trying to find/replace different parts of the procedure body.
If you are going to send a script, why not send the script for ALTER PROCEDURE, tested and working perfectly , instead of the dynamic thing you posted a piece of...i assume that after you get the proc body, you would execute(NewSP)? inside a cursor or something? teh cursor may or may not find all the items you intend to replace...
i
Lowell
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply