how to change objects type - SQL Server Forums

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 Administration (2000)

how to change objects type

Author

Topic

dejjan
Yak Posting Veteran

99 Posts

Posted - 2005-05-31 : 02:54:22

Hello,
how could I change type for stored_procedure, I created on master.
I want to be 'system', not 'user'.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-31 : 03:19:03

There is an undocumented procedure that will do this, sp_MS_upd_sysobj_category.

EXEC master.dbo.sp_MS_upd_sysobj_category 1

GO

Create Procedure YourProcName

AS

-- blah

GO

EXEC master.dbo.sp_MS_upd_sysobj_category 2 -- off

GO

Damian
Ita erat quando hic adveni.

dejjan
Yak Posting Veteran

99 Posts

Posted - 2005-05-31 : 03:41:33

this is great.

Thanks

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-05-31 : 14:41:45

Hmmm... Never having had the desire to flag a procedure as system, I see this as a learning opportunity. Can anyone give me a good reason why you would want to do this?

---------------------------
EmeraldCityDomains.com

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2005-05-31 : 19:41:01

So it looks neat in enterprise manager

Damian
Ita erat quando hic adveni.

donpolix
Yak Posting Veteran

97 Posts

Posted - 2005-06-01 : 15:15:24

Neat as in you wont even see them when you intend to not display the system tables?

Donn Policarpio

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-06-02 : 11:08:52

I have a similar problem I have been fighting with for last couple days. I created a table in dev server, which has dbo as owner. However, when I DTSed it into production, somehow it showed up as system.tableName.

Then when I tried to fix it with this,
EXEC sp_changeobjectowner 'system.TableName', 'dbo'

I got:
Caution: Changing any part of an object name could break scripts and stored procedures.
And nothing happen.

I don't know how I got into this mess, and definitely would appreciate any help to get me out of it.

Does Merkin's undocumented sp work in this case as well?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources