Need help changing Owners

  • 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

     

  • 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