June 1, 2007 at 3:30 pm
Hi,
I have alot of views within a database. I know how to change the owner of the view itself but I need a script to change the inside i.e. when I go to design/modify, it is referencing to the wrong owner like proddta.table1 I want to change all the views to be changed to crpdta.table1
Can someone send me a script that can do that? I would really appreciate it.
Thank you.
Andre
June 1, 2007 at 7:53 pm
You could right-click on the view in the object browser and select Edit...the resulting script should be an ALTER VIEW script with the correct schema name. It would require at most a quick edit before executing.
If you really want a script to do it, this will both change the view owner and fix the definition. It takes the existing definition and replaces the existing "CREATE VIEW ..." with "ALTER VIEW ..."
set
nocount on
declare @OldSchema sysname, @NewSchema sysname, @ViewName sysname, @cmd1 varchar(max), @cmd2 varchar(max)
select @OldSchema = 'abc', @NewSchema = 'def', @ViewName = 'whatever'
select * from information_schema.views where table_schema = @OldSchema and table_name = @ViewName
set @cmd1 = 'ALTER SCHEMA ' + quotename(@NewSchema) + ' TRANSFER ' + quotename(@OldSchema) + '.' + quotename(@ViewName)
select @cmd2 = STUFF(view_definition, p1, p2-p1, 'ALTER VIEW ' + view_name + ' ')
from (
select view_name, view_definition, p1, charindex('AS', view_definition, charindex(@ViewName, view_definition, p1)) as p2
from (
select quotename(@NewSchema) + '.' + quotename(table_name) as view_name,
view_definition, charindex('CREATE', view_definition) as p1
from information_schema.views where table_schema = @OldSchema and table_name = @ViewName
) y
) x
exec (@cmd1)
exec (@cmd2)
select * from information_schema.views where table_schema = @NewSchema and table_name = @ViewName
This was a quick-and-dirty effort with no error handling, use at your own risk. The parsing assumes that the first occurrence of "CREATE" is the CREATE VIEW statement, if there are leading comments that include the word CREATE it will screw up.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply