April 20, 2015 at 8:46 am
I am trying to replace object name in views , triggers, stored procs, UDF,TVF etc.
I have created a automated script to replace 'dbo.Cust' with 'dbo.Customer' in all objects and generate script as ALTER Statements.
some objects are still scripted out as Create. Reason is it has some extra space in between
CREATE TABLE #test1(
[NAME] [nvarchar](128) NOT NULL,
[DEFINITION] [nvarchar](max) NULL,
[DEFINITION_bk] [nvarchar](max) NULL,
[type] [char](2) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
INSERT INTO #test1
SELECT NAME
,DEFINITION
,DEFINITION
, type
FROM sys.all_objects AO
INNER JOIN sys.sql_modules SM ON SM.object_id = AO.object_id
WHERE
type IN (
'TR' ,'FN' ,'P '
,'TF' ,'R ' ,'IF' ,'V' )
AND NAME NOT LIKE 'dt_%'
AND NAME NOT LIKE 'sys_%'
and DEFINITION LIKE '%' + REPLACE(REPLACE('dbo.cust',']','\]'),'[','\[') + '%' ESCAPE '\'
SELECT * FROM #test1
where DEFINITION like '%create%'
UPDATE #TEST1
SET DEFINITION = CASE WHEN TYPE = 'TR' THEN REPLACE(DEFINITION,'CREATE TRIGGER', 'ALTER TRIGGER')
WHEN TYPE IN ( 'FN','TF','IF') THEN REPLACE(DEFINITION,'CREATE FUNCTION', 'ALTER FUNCTION')
WHEN TYPE = 'P ' THEN REPLACE(DEFINITION,'CREATE PROCEDURE', 'ALTER PROCEDURE')
WHEN TYPE = 'R ' THEN REPLACE(DEFINITION,'CREATE RULE', 'ALTER RULE')
WHEN TYPE = 'V' THEN REPLACE(DEFINITION,'CREATE VIEW', 'ALTER VIEW') END
April 20, 2015 at 8:54 am
mxy (4/20/2015)
I am trying to replace object name in views , triggers, stored procs, UDF,TVF etc. created a automated script based on Search condition is 'xyz' replace with pqr in all objects.for some reason I was not able to replace all. if create procedure has extra space or when it is create proc instead of procedure . I was not able generated automated script with alter statements, still some of the objects are not altered. Any help would be appreciated.
SELECT NAME
,DEFINITION
,DEFINITION
, type
FROM sys.all_objects AO
INNER JOIN sys.sql_modules SM ON SM.object_id = AO.object_id
It's not 100% clear what sort of answer you want, other than a 'helpful' one. Can you be more specific about what you want?
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
April 20, 2015 at 9:11 am
sorry about that edited and provided the code I am working
April 20, 2015 at 9:21 am
Part of the issue is that the code you alter isn't the object. The object is compiled by SQL Server.
What you really need to do is get all the object code, do the search/replace, and re-execute (recompile) it on the instance.
There isn't an easy way to do this. It's an easy process, but cumbersome. If your code were in a VCS, you could check everything out, do a global search/replace, and then execute all the code again. That could be done with Powershell, SQLCMD, maybe SSMS, or other techniques.
I might recommend tackling this first by scripting all objects out to a file and then searching replacing. You can then look at how to execute changed objects.
April 20, 2015 at 9:31 am
ok thanks I got to two levels upto get objects that I need to alter, replace parameter . issue is with making alter statements instead of create.
if we cannot do with TSQL then I will generate all objects as alters and search/ replace as you said.
Thanks.
April 20, 2015 at 10:07 am
The way I would do it is closely related to Steve's suggestion:
1) Get the database code scripted out using VS or Redgate SQL Compare.
2) Get it into a VCS.
3) Devise a way of searching and replacing in your source files using Regular Expressions (I have written a C# console application to do this).
4) Update your source files as required.
5) If your DB was build using VS database projects, confirm that it still builds after the source file changes.
6) Do a schema compare between your updated source files and your database, to check that the changes work how you want them to work.
7) As you're using a VCS, you can 'undo' your changes to reset them while you are developing your replacements, for rapid testing without changing database objects. So you can run your replace code again and again while refining it.
8) Generate an 'apply' script using your schema comparison tool & see how it looks. Particularly watch out for table or column drops/recreates!
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply