sql2008 - Better than multiple replace statements?

Question

Further to my previous post, I would like to copy dependent objects (such as views or procedures) to a 'static' database. However, schema names and other object prefixes are not the same between Production and Static databases...

[I've read Aaron Bertrand's articles on setting up an Audit database, but this is a little much for our needs at this time.]

After extracting the object definitions into a variable using some dynamic sql, I am running multiple replace statements for each change so that the views/procedures still run, pulling data from the Static database.

The reason for the replace statements is that the views/procedures have been created using differing naming conventions. Sometimes I find <dbname>.dbo.<objectname>, other times it's <dbname>..<objectname> or even just dbo.<objectname> !

Instead of using multiple replace statements as below (I feel this may grow quite large!), is there a better method? Would a table-driven approach (using a CURSOR) be wiser/wisest?

[Database/object names have been modified in the code below for simplicity]

declare @sql nvarchar(500), @parmdef nvarchar(500), 
        @dbname varchar(20), @objname varchar(255), @ObjDef varchar(max);
set @dbname = 'ProdC';

--declare cursor; get object name using cursor on dbo.ObjectsToUpdate
--[code removed for simplicity]

set @sql = N'USE '+quotename(@dbname) +'; ' ;
set @sql = @sql + N'SELECT @def=OBJECT_DEFINITION(OBJECT_ID(''dbo.'+@objname+ '''));'
set @parmdef = N'@def nvarchar(max) OUTPUT' ;
exec sp_executesql @sql, @parmdef, @def=@ObjDef OUTPUT;

--Carry out object definition replacements
set @ObjDef= replace(@ObjDef, 'CREATE VIEW [dbo].[', 'ALTER VIEW ['+@dbname+'].[');
set @ObjDef= replace(@ObjDef, 'Prod1.dbo.', @dbname+'.'); --replace Prod1 with @dbname
set @ObjDef= replace(@ObjDef, ' dbo.', ' '+@dbname+'.'); --replace all 'dbo.'
set @ObjDef= replace(@ObjDef, 'dbo.LookupTable1', @dbname+'.LookupTable1');
--[code removed for simplicity]

exec(@ObjDef);

--get next object name from cursor
--[remaining code removed for simplicity]

Many thanks in advance.

score 1 · Answer 1 · edited Nov 10 '18 at 16:10

1

Another problem that you have is using OBJECT_DEFINITION. This returns only the first 4,000 characters of the object name.

The same problem exists using INFORMATION_SCHEMA.ROUTINES.

Check out this post for a discussion of the alternatives...

Dude, where's the rest of my procedure?

edited Nov 10 '18 at 16:10

Aaron Bertrand

272,866
37
466
490

answered Apr 19 '12 at 15:30

Gordon Linoff

1,242,037
58
646
786

2

thanks for the response but I think you'll find it's just the opposite for `OBJECT_DEFINITION()`. I could use 2 ways to return the definition, the other is to query `sys.sql_modules`. Both methods return `nvarchar(max)`... – MarkusBee Apr 20 '12 at 08:16

score 0 · Answer 2 · answered Apr 27 '12 at 14:40

"However, schema names and other object prefixes are not the same between Production and Static databases"

That's your problem right there. Make them the same and your problem will go away. With SQL Server supporting multiple instances there should be no barrier to doing that.

score 0 · Accepted Answer · edited May 23 '17 at 10:08

0

@ben: the Static database pull from several Production databases and there is a desire to maintain the original 'source' by using the database name as schema name in the Static database.

Closed. Duplicate with this post

edited May 23 '17 at 10:08

Community

1
1

answered May 04 '12 at 13:15

MarkusBee

142
1
3
11

sql2008 - Better than multiple replace statements?

3 Answers3