Tables marked as System Table

Question

Tables marked as System Table

mgrammer

Valued Member

Points: 52
More actions
March 20, 2009 at 5:06 pm

#202548

I am having an issue with a few of the tables in multiple databases. I have recently started moving databases from sql 2000 to 2005 and shortly after noticed that my tables are showing up as System Tables. I am simply detaching the database from sql 2000 and attaching to 2005.
I would like to know the best way to mark these tables as non system tables. I know that updating the sysobjects table and modifying the table directly is not an option.
Any help would be greatly appreciated.

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

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

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

Attaching a SQL Server 2000 database to 2005 does not change tables from user to system. How are you determining that your tables are now marked as system tables?

Run the following:

Use {your database};

Go

Select *

From sys.tables;

Your tables should all have a type of 'U' and the type_desc will be 'USER_TABLE'.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

mgrammer Valued Member Points: 52 More actions · Answer 2

They do have a type of U and a type_desc of USER_TABLE but is_ms_shipped is marked as 1 and I believe it should be 0. I realize that attaching the DB didnt cause the issue and must be instead related to a prior mistake.

maechismo_8514 SSCoach Points: 17510 More actions · Answer 3

mgrammer (3/20/2009)
They do have a type of U and a type_desc of USER_TABLE but is_ms_shipped is marked as 1 and I believe it should be 0. I realize that attaching the DB didnt cause the issue and must be instead related to a prior mistake.

Strange innit?. How's that possible for sql server to change user tables to system tables when upgraded? What are your attach and detach comments? Did you perform backup and restore to see it does the same thing?

Steve Jones - SSC Editor SSC Guru Points: 736255 More actions · Answer 4

I was searching around because I know someone reported something similar awhile back. I don't think it hurts anything to have that bit flipped, but please post a note if you determine something about why.

If you run a backup and restore to another server, are they still flipped?

maechismo_8514 SSCoach Points: 17510 More actions · Answer 5

Steve,

all the user tables have is_ms_shipped=0 and all the systemtables generated by sql server have is_ms_shipped=1 but this bit is not always set correctly as far as I know and nothing on BOL about this bit too though it shows on your result set.

mgrammer Valued Member Points: 52 More actions · Answer 6

I tried a backup and restore but the tables remain as system tables.

I guess if all else fails I can export the data to another server, remove the current tables, recreate the tables with a script then re-import the data. It just seems like a lot to go through to have these tables show as non system tables.

mgrammer Valued Member Points: 52 More actions · Answer 7

I thought I would go ahead and add a couple images showing whats going on.

This shows my tables as both system and non system tables.

This shows where is_ms_shipped is set incorrectly.

Steve Shurts SSC Veteran Points: 292 More actions · Answer 8

This is a bug that was reported to Microsoft in 2006. (Timely attention to bugs...) The suggested solution at that time was the following:

You have uncovered a bug in the product. Thank you for reporting this. We will fix this in an upcoming service pack.

As a workaround, set the allow updates option to 0 and re create your procedures. This should set the is_ms_shipped bit to 0. Let us know if that does not work for you.

--To turn allow updates to 0, use the following.

use master

go

exec sp_configure 'allow updates' , 0

go

reconfigure with override

go

I also so a post that calls it "by design" - I think that's Microsoft-speak equivalent to IBM-speak "it's a feature". Check here for more info: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=278520

mgrammer Valued Member Points: 52 More actions · Answer 9

Thanks Steve.

I went ahead and ran that statement. I'm assuming that will prevent any future tables from being marked as system, but unfortunately the existing tables did not change.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 10

mgrammer (3/23/2009)
Thanks Steve.
I went ahead and ran that statement. I'm assuming that will prevent any future tables from being marked as system, but unfortunately the existing tables did not change.

No, just doing that by itself won't change anything. This was directed at stored procedures - and the recommended solution was to turn off the option to allow updates and then recreate the procedures.

Since you cannot recreate the tables the same way - it is going to take a bit more work.

One thing to verify first is whether or not these tables were marked in 2000. If so, then you need to fix it in 2000 before upgrading to 2005.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

mgrammer Valued Member Points: 52 More actions · Answer 11

Thanks everyone for the help. It looks like it will not be a simple fix :crying: .

Even though I still have the tables stored on our SQL 2000 SVR they are now out of date and fixing the issue there and then moving over to 2005 is no longer an option, without getting them back up to date.

Instead I am just going to script the tables then re-import the data.

Thanks again.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 12

mgrammer (3/23/2009)
Thanks everyone for the help. It looks like it will not be a simple fix :crying: .
Even though I still have the tables stored on our SQL 2000 SVR they are now out of date and fixing the issue there and then moving over to 2005 is no longer an option, without getting them back up to date.
Instead I am just going to script the tables then re-import the data.
Thanks again.

Sorry to hear that - but, could you confirm whether or not the problem existed in 2000? I would just like confirmation that this is not something that could have happened during the upgrade.

Thanks.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Steve Shurts SSC Veteran Points: 292 More actions · Answer 13

In order to get the current objects to lose the system attribute, you would have to script them out, drop them and recreate them. In order to do this with tables containing data, you would have to be a bit more creative. For example, you could script the table with a slightly different name, load the data from the current table, drop the current table and rename it using the rename object procedure. If the tables are empty, script them and drop them.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 14

In addition to what Steve has outlined, you are going to have to disable/drop foreign keys before you can recreate the tables. And, don't forget to script the indexes also.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs