Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, July 22, 2009 9:22 AM
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

 

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

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:04 PM
Points: 2,845, Visits: 1,158

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.




Post #370827
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse