CODEWITHSUNDEEP
sqlsql-server-2008t-sqlreplace
MarkusBee
MarkusBee

Reputation: 142

sql2008 - Better than multiple replace statements?

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.

Upvotes: 0

Views: 615

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1270431

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...

Upvotes: 1

MarkusBee
MarkusBee

Reputation: 142

@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

Upvotes: 0

Ben
Ben

Reputation: 35643

"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.

Upvotes: 0

Related Questions