replace all tables used in view with prefix and update view defn automatically

Question

Post reply

replace all tables used in view with prefix and update view defn automatically

skb 44459

SSCarpal Tunnel

Points: 4753
More actions
November 2, 2021 at 4:55 pm

#3947179

I would like to change definition of all views and want to replace all tables used in view definition with prefix.

if my view is create view ki_co as select * from co

I would like this to be changed to select * from Mst_co.

I want to update all my views automatically. is this possible ?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

ratbak SSC Eights! Points: 939 More actions · Answer 1

Not automatically. You could view object details right click to generate alter scripts (or create & then replace "CREATE VIEW" with "ALTER VIEW"), and then just do find-and-replace.

Easy if everything is separated by a space; more complicated if there are variations with CR/LF, indents, tab instead of space, or multiple spaces.

Would this apply to joins in views too (e.g., JOIN co, INNER JOIN co, LEFT OUTER JOIN co, etc.)?

You might want to take the opportunity to prefix table names with schema too while you're at it (presumably dbo?).

ScottPletcher SSC Guru Points: 100262 More actions · Answer 2

Below is some code that will probably do what you want. I strongly recommend you run it first with the EXEC() commented out. If all of the output looks ok, then uncomment the EXEC() and run it again.

Instead, if table co is going away, you could create a synonym for co that points to Mst_co.

SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;

DECLARE @location int;
DECLARE @object_id int;
DECLARE @object_name nvarchar(128);
DECLARE @sql nvarchar(max);

DECLARE view_cursor CURSOR LOCAL FAST_FORWARD FOR
SELECT referencing_id AS object_id, OBJECT_NAME(referencing_id) AS object_name
FROM sys.sql_expression_dependencies sed
WHERE OBJECTPROPERTYEX(sed.referencing_id, 'BaseType') = 'V' AND
    referenced_entity_name = 'co'

OPEN view_cursor
WHILE 1 = 1
BEGIN
    FETCH NEXT FROM view_cursor INTO @object_id, @object_name
    IF @@FETCH_STATUS <> 0
        BREAK;
    SET @sql = OBJECT_DEFINITION(@object_id)
    SET @sql = REPLACE(REPLACE(@sql, ' FROM co', ' FROM Mst_co'), 'FROM dbo.co', 'FROM dbo.Mst_co')
    SET @location = CHARINDEX('CREATE VIEW ', @sql)
    IF @location < CHARINDEX('ALTER VIEW ', @sql)
        SET @sql = STUFF(@sql, @location, 11, 'ALTER VIEW')
    SELECT @sql AS [/*sql_to_exec*/]
    --EXEC(@sql)
END 
DEALLOCATE view_cursor

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

Sorry... post withdrawn. Wrong post.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions