Table types

Question

Table types

Louis Fritz-153882

SSC Enthusiast

Points: 166
More actions
January 12, 2005 at 11:19 am

#74838

I have a developer that created two tables and the table type is system. How can I change the type to USER?

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

I've never heard of a user creating a system table. How did he do that?

Also I don't think it's possible to do this (because you could also remove other valid system tables...). But I'm not on the guru team so I'll step back.

Antares686 SSC Guru Points: 125444 More actions · Answer 2

It can be done to mark a table as a system object but that requires going out of your way to upset the sysobjects table manually or use sp_MS_marksystemobject procedure which is not documented.

You should be able to delete and recreate these tables but there are issues trying to change it back to a user table once they have been marked.

Are you sure these aren't really system tables?

Louis Fritz-153882 SSC Enthusiast Points: 166 More actions · Answer 3

Yes, I sure they are not system tables. And I can not delete the table because it is a system.

We looked in the sysobjects table and the object type is U.

I think the only thing I can do is go back to a an old backup of the DB.

Thanks for your help

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

I've only heard it this far from procedures and functions. AFAIK, loses a procedure this status once it is changed after being marked as system object, so that it needs to be marked again. Maybe this will also happen here with these tables. So, I would try to add a column and inmediately delete it again.

Anyway, I would be very interested in how your developer actually did this. May you ask him and post his answer here?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

That will do it... won't be fast but it won't cost you any data :

Use Master

GO

CREATE TABLE dbo.TABLE1

(

PkTest char(10) NULL

) ON [PRIMARY]

GO

Select name, XType, Status from dbo.SysObjects where name = 'TABLE1'

--stop here and check the table type (user)

exec sp_MS_marksystemobject 'TABLE1'

--stop here and check the table type (system)

Select name, XType, Status from dbo.SysObjects where name = 'TABLE1'

Select * Into TABLE2 from Table1

GO

DROP TABLE TABLE1

GO

Select name, XType, Status from dbo.SysObjects where name = 'TABLE2'

exec sp_rename 'TABLE2', 'TABLE1', 'OBJECT'

--stop here and check the table type (user)

--DROP TABLE TABLE1

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

Now that's disappointing, Remi!

I thought it to be more tricky, thrilling and exciting.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Sorry carpal tunnel... I'll try to find an harder solution next time .

BTW Antares had already pointed in the direction of the ms proc... I simply put 2 and 2 together.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Hi Veteran

Yes, I expect you to do so!!! This proc is nice, isn't it? (maybe because of this it's undocumented)

Btw, just call me Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

I think I'll stick to a shorter name...

I'll call you Capt. Carp. from now on.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

I'll call you Capt. Carp. from now on.

Feel free to do so, if it makes you happy

Once you cross the 4,000 posts barrier, expect me to find a bitchin' (hehe, applied knowledge transfer from Adam. Hopefully in the right context.) name for you

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Might not seem like it but I have other things to do in life...

Maybe in the year 2010 I'll be there.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 12

That's okay with me. I keep things in mind just like an elefant

And some more of such off-topics (now) threads, and you'll be sooner there as you might think.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 13

Now back to the topic.

Remi,

just realized that this makes a good conribution for my site. Do you mind me referencing your script along with your name for your higher virtual fame?

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

No I don't mind. As long as the headline read something like this :

Capt. Carp outwitted by a 10 months old gold fish.

Or anything among those lines