Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need help changing Owners


Need help changing Owners

Author
Message
Andre-427668
Andre-427668
Grasshopper
Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)Grasshopper (11 reputation)

Group: General Forum Members
Points: 11 Visits: 30

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


Scott Coleman
Scott Coleman
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3104 Visits: 1442

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.





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search