Changing the table names in view definition for multiple views

Question

Changing the table names in view definition for multiple views

neela.teki

SSC Journeyman

Points: 97
More actions
June 11, 2016 at 9:17 pm

#327167

I need help on how to change the view definition (change the name of the tables) based on view name and current date.
Example:
View Name: v_ABC_LAST_THREE
CurrentDate: 2016-06-01
View definition: as of 2016-05-01
Select * from ABC_201603
UNION ALL
Select * from ABC_201604
UNION ALL
Select * from ABC_201605
I would need to modify name of tables used in view definition to as per the suffix used in view name (Last_Three) and current date. View definition should be like this after modification.
View definition: as of 2016-06-01
Select * from ABC_201604
UNION ALL
Select * from ABC_201605
UNION ALL
Select * from ABC_201606
I have more than 100 views to modify like this with different suffixes (Last_Four,Last_twelve etc)
So far I tried scripting the view using ‘exec sp_helptext’ and tried to search the table name and replcing it with new name. No success so far. My pregnancy brain is tricking me :angry: When I loop though three times I ended up with same table name for all the three tables :unsure: Any ideas will be a great help.
DECLARE @ID INT
, @searchFor VARCHAR(100)
, @replaceWith VARCHAR(100)
,@CurMonth VARCHAR(6)='201606'
DECLARE @view TABLE (ID int identity(1,1),Viewname VARCHAR(1000),TableName VARCHAR(1000),SearchFor VARCHAR(1000), ReplaceWith VARCHAR(1000))
INSERT INTO @view VALUES
('vw_ABC_LAST_THREE','ABC','ABC_201603','ABC_201604')
,('vw_ABC_LAST_THREE','ABC','ABC_201604','ABC_201605')
,('vw_ABC_LAST_THREE','ABC','ABC_201605','ABC_201606')
DECLARE @temp TABLE (spText VARCHAR(MAX), ID int identity(1,1))
INSERT INTO @temp
exec sp_helptext 'dbo.vw_ABC_LAST_TWO'
INSERT INTO @temp
VALUES ('GO')
DECLARE curHelp CURSOR FAST_FORWARD
FOR
SELECT SearchFor, ReplaceWith FROM @view
OPEN curHelp
FETCH next FROM curHelp INTO @searchfor,@Replacewith
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE t
SET spText = REPLACE(spText,@searchFor, @replaceWith)
FROM @temp t
FETCH next FROM curHelp INTO @searchfor,@Replacewith
END
CLOSE curHelp
DEALLOCATE curHelp
Select * from @temp

Viewing 6 posts - 1 through 5 (of 5 total)

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

DouglasH SSCommitted Points: 1669 More actions · Answer 1

The reason you get three tables with the same name is that the cursor loops 3 times and overwrites the change from the previous loop.

You can fix that by putting an 'order by' in the cursor code to reverse the order of looping - (or by changing the order in the @view table, since you created it by hand).

neela.teki SSC Journeyman Points: 97 More actions · Answer 2

Thanks douglasH. I knew it would be a simple fix and I was close to getting the code work.

Do you have any ideas to update view definition(table names in ) without the temp table I created with searchfor and replacewith columns manually?

DouglasH SSCommitted Points: 1669 More actions · Answer 3

I suppose you have 3 approaches

1. Continue with your current solution. Write the table out manually (It's 100 views so, yes, it's boring and liable to mistakes but you can do it). You could do it in Excel because it'll suggest the text as you type each letter. Or- Write some SQL to create the table - That's reasonably tricky because you have to parse the SQL Server system tables or what you get back from sp_helptext.

2. use dynamic sql and string handling to build all your views from scratch. You'd need to make sure that there are common patterns to generate the names of views, and the code within. For example, for a view that ends 'LASTTHREE', get the month for the current date as a number and generate each of the clauses for the previous 3 months, with 'UNION ALL' added between them.

3. Use a different approach. Examine what the views are used for and find a way to pass the date to whatever calls the views and use it in a 'where' clause because it seems that these views are being generated to limit the data searched but if you have good indexes and proper 'where' clauses, you won't need these generated views. Just guessing of course, you'll know the needs of your system.

neela.teki SSC Journeyman Points: 97 More actions · Answer 4

Thanks for your inputs DouglasH.

1.I chose this approach as I'm good with excel and it was easy for me to create the data I needed in a table and imported data from excel to table in no time.

2. looks like lot of work to me compared to approach 1:unsure:

3. There are complex data imports and cubes based on these views and our ETL process is very complex and any modifications/additions to it are not allowed :rolleyes:

Thanks for your time to help me on this ??

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 5

~~Wouldn't it be a whole lot easier to leave the view definitions alone and use a synonym instead?~~

Edit... sorry. Too quick a look. :blush: Looking again.

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