Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Need help changing Owners Expand / Collapse
Posted Friday, June 1, 2007 3:30 PM


Group: General Forum Members
Last Login: Wednesday, July 22, 2009 9:22 AM
Points: 11, Visits: 30


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.




Post #370805
Posted Friday, June 1, 2007 7:53 PM


Group: General Forum Members
Last Login: Tuesday, November 29, 2016 11:30 AM
Points: 2,936, Visits: 1,410

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'

* from information_schema.views where table_schema = @OldSchema and table_name = @ViewName

@cmd1 = 'ALTER SCHEMA ' + quotename(@NewSchema) + ' TRANSFER ' + quotename(@OldSchema) + '.' + quotename(@ViewName)

@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 (@cmd2)

* 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.

Post #370827
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse