How could i changed ALL the table owners who aren'

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

How could i changed ALL the table owners who aren'

Author

Topic

mbevon
Starting Member

41 Posts

Posted - 2002-07-05 : 09:55:50

How could i changed ALL the table owners who aren't DBO owned?

thanks

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-07-05 : 10:25:09

The following script from (http://vyaskn.tripod.com/generate_scripts_repetitive_sql_tasks.htm) will generate the required commands for changing the owner of all non-dbo owned table sto dbo. Just copy the output and execute it.

SELECT 'EXEC sp_changeobjectowner ''' + TABLE_SCHEMA + '.' + TABLE_NAME + ''', dbo'
FROM INFORMATION_SCHEMA.TABLES
WHERE
OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
AND TABLE_TYPE = 'BASE TABLE'
AND TABLE_SCHEMA <> 'dbo'
GO

After changing the ownership, you will see the following warning message:
Caution: Changing any part of an object name could break scripts and stored procedures.

--
HTH,
Vyas
http://vyaskn.tripod.com

mbevon
Starting Member

41 Posts

Posted - 2002-07-05 : 11:05:37

Hiii VyasKN

I'd a look at your site, it's pretty good, i'm a cricket fan too, i'm a fanatic when Sachin is batting, i know you won't agree but Brian is better technically but when it comes to comparison Sachin is the Greater ( and could become the greatest, just imagine he was playing in Bradman days, he' would of been averageing over 150) because he has that focus winnig attitude.

Now for some sql,
Should i put the code in a query and run it?
what's all about the message?

thanks
bro

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-07-07 : 14:26:58

bro, in my humble opinion, Brian Lara is a goner ;-) and Sachin is stil going strong (Made a century the other day) :-)

Coming to your question, yes, you'll have to run that code in Query Analyzer. Copy the output and paste it in the Query window. And run the code that you've just pasted.

--
HTH,
Vyas
http://vyaskn.tripod.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources