February 20, 2009 at 12:37 am
Hi All,
I have one scenario.
Say for example,
create view v1
as
begin
select eno,ename,ecode
from emp
Assume, i am using this view in some other views.
create view v_ename
as
begin
select ename
from v1
where ecode like 'A%'
create view v_eno
as
begin
select eno
from v1
where ecode like 'Z%'
Coming to my issue, now i have added 2 columns in view v1. it should be reflected or added in all the views which are using view v1.
create view v1
as
begin
select eno,ename,ecode,age,salary
from emp
age & salary should be added in all the dependant view v1.
i.e v_ename & v_eno should reflect the newly added columns.
The problem is if we have less no of views, we can open al the views and add the required fileds if necessary,but since there are around 150 views in the database, it is impossible to open each and every view to know whether it uses view v1 or not. Right?
How to add those 2 new columns in view v1's dependant views?
Inputs are welcome!
karthik
February 20, 2009 at 8:34 pm
This should help you identify the set of views which need to be changed. I cringe at the idea of views nested within views, but good luck.
select so.name as viewName
from syscomments sc
join sysobjects so on sc.id = so.id and so.type = 'V'
where text like '%yourViewName%'
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
February 23, 2009 at 6:01 am
Thanks Bob for giving the workaround!
But i am looking for specific commands or the correct way to get it.
karthik
February 23, 2009 at 10:32 am
Okay... you have a list of the views already, next:
Put the names and object ids of the views into a temporary table.
Loop through it if you want, since you won't be doing this often.
Get the CREATE VIEW text for each view into a variable (@SQL).
(select * from syscomments where object_name(id) = 'NameOfYourView')
Multiple rows may be returned if text is large. See the colID column for sequence of
stitching them back together.
Do a little search/replace magic to make CREATE VIEW into ALTER VIEW
Find the end of the column list and add your two new columns
Execute the variable as dynamic SQL
exec sp_executesql @sql
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 5, 2009 at 4:12 pm
thx for the tips
i have one question on the same topic though, am i right in assuming that if you have nested views (inside views) you will have to update the nested views after the "main" views, otherwise the new columns will still not show in the nested views ?
for example if v1 is as follows :
select * from table1
and v2 like :
select * from v1
then if table1 columns get modified you will have to refresh v1 before v2 if you want to get all the table1 columns in v2 ?
in that case do you have a "simple" way to make sure the view dependencies are properly sorted so nested views are updated last ?
thx
March 5, 2009 at 4:48 pm
A simple way? None comes to mind.
However, the solution would center around the original code I posted. Off the top of my head I would write a loop (shudder) that would start with a single view and give it a level of 1, then get the list of it's dependents and give them a level of 2, then get THEIR dependents until finally no rows were returned for dependents. Probably could do the same thing with a recursive CTE.
After exiting the loop, I would run a summary query that set to get a list of the views and their MAXIMUM level, and then rebuild the views in that order. That would be my plan anyway. I'm afraid I don't have time to code and debug a code sample right now, but if you want to try it on your own and run into problems, start another thread with any questions. That way you can get more eyes than mine on the problem.
If anything simpler occurs to me overnight, I will post it here.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
March 6, 2009 at 2:36 am
thx that's what i've been doing on one ugly database i have to manage which is filled with views, i am using a fixed 2 level down search knowing there aren't any dependencies deeper than that.
I'll rewrite it using your syscomments text search because i was using only sysdepends and for some reason it seems that sometimes my code was missing some dependencies, or maybe the dependencies went missing after some drop/create but that seems doubtful.
thx for the insights
March 6, 2009 at 10:16 am
One nice thing about searching syscomments is that it SOMETIMES catches a string being built in dynamic SQL.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 20, 2009 at 3:00 am
just wanted to complete this thread by pointing out that sysdepends is not reliable and in some cases when you drop / create objects not in a correct order then dependencies between your objects will be missing !
Thus, so far the only "simple" way i found to make sure i get all the dependencies for an object is indeed to scan syscomments.text for that object name.
The main downside of that solution is that it will find more objects than what you're looking for if you use object names with similar patterns.
If you finally find the missing objects from a syscomments search you can repopulate your sysdepends table by altering your object again. I was told that you'd need to actually drop/create the object but i found out that altering it (without changes to the code) was enough although it may not be the case for all objects, i can't tell for sure..
Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply