change owner on multiple views

I have found code that will loop through all tables and change the owner, but can someone provide the code that would loop through all views and change owners?
wppiexpertsAsked:
Who is Participating?
 
Aneesh RetnakaranDatabase AdministratorCommented:
DECLARE @dbObject VARCHAR(255)
DECLARE CurDbObjects CURsoR FOR
SELECT su.NAME + '.' + so.NAME AS OBJECT
FROM sysobjects so , sysusers su
WHERE so.UID = su.UID AND su.NAME <> 'dbo'      -- will search for objects owned by other than dbo,
AND su.NAME NOT LIKE 'INFORMATION%' AND XTYPE IN('U','V')
ORDER BY so.NAME
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
DECLARE @dbObject VARCHAR(255)
DECLARE CurDbObjects CURsoR FOR
SELECT su.NAME + '.' + so.NAME AS OBJECT
FROM sysobjects so , sysusers su
WHERE so.UID = su.UID AND su.NAME <> 'dbo'
AND su.NAME NOT LIKE 'INFORMATION%' AND XTYPE IN('U','V')
ORDER BY so.NAME

OPEN CurDbObjects
FETCH NEXT FROM CurDbObjects INTO @dbObject
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC ('sp_changeobjectowner ''' + @dbObject + ''' , ''dbo''')
FETCH NEXT FROM CurDbObjects INTO @dbObject END
CLOSE CurDbObjects
DEALLOCATE CurDbObjects
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
the above code will change the ownership of all objects to 'dbo'
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
wppiexpertsAuthor Commented:
does the above code allow me to search for a specific owner and change it to something else? I don't want something that will do a global change because some dbo owned objects need to stay that way.

(sorry that I wasn't clear on that)

also, I did run the code and changed the first instance of dbo to the owner name I wanted changed and then changed the second instance of dbo to what I wanted changed to, unfortunately, none of the views were affected by this.

0
 
wppiexpertsAuthor Commented:
can tables be included as well through the xtype list? if so, what is the code for that?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
wppiexperts,
> n tables be included as well through the xtype list?
Yes, Its already included in the statement
'U' -> User table
'V' -> View
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.