Marking a table as a System Table

[Damien]

Okay, most peoples answer to this may be "Gaaah. Why would you do
this?", or the like, but here's the question anyway:

Are there any stability issues if I mark one of my user tables as a
system table (by switching xtype in sysobjects from 'S' to 'U')?

I'm not doing this as "a cleved bit of security" or some such - my
actual reason for doing this is so that some of my automatic generation
tools do not process this particular table, and I want a method that
will not mean updating each of the tools if I ever add another table
like this.

It APPEARS to work, based on a quick trial, but has anybody got any
direct experience of this? Any horror stories like "Well, it worked
fine for two weeks, then it shot my co-workers, set fire to the company
accounts, and urinated in a corner. Then things got worse"

Also, yes, yes, yes, I do not necessarily expect this to work in future
releases of SQL Server (currently on 2000), but I should avoid naming
conflicts by the fact that the owner isn't dbo.
Thanks in advance for any insights.

[Craig Kelly]

"Damien" wrote:

Okay, most peoples answer to this may be "Gaaah. Why would you do
this?", or the like, but here's the question anyway:

Are there any stability issues if I mark one of my user tables as a
system table (by switching xtype in sysobjects from 'S' to 'U')?

I'm not doing this as "a cleved bit of security" or some such - my
actual reason for doing this is so that some of my automatic generation
tools do not process this particular table, and I want a method that
will not mean updating each of the tools if I ever add another table
like this.

It APPEARS to work, based on a quick trial, but has anybody got any
direct experience of this? Any horror stories like "Well, it worked
fine for two weeks, then it shot my co-workers, set fire to the company
accounts, and urinated in a corner. Then things got worse"

Also, yes, yes, yes, I do not necessarily expect this to work in future
releases of SQL Server (currently on 2000), but I should avoid naming
conflicts by the fact that the owner isn't dbo.
Thanks in advance for any insights.

Well I changed a system table once and all the workers recovered, the
building only smoldered, no one noticed the puddle, and then things seemed
to clear up on their own :)

Seriously, though, why not use extended properties for this purpose? For
your table that you don't want to process, use...

exec sp_addextendedproperty
'DoNotProcess',
'1',
'USER',
'dbo',
'TABLE',
'TableThatIsntProcessed',
NULL,
NULL

....Then before you process any table you can...

if not exists (
select null
from ::fn_listextendedproperty(
'DoNotProcess',
'USER', 'dbo',
'TABLE', 'TableThatIsntProcessed',
NULL, NULL
)
where value = '1'
)
begin
print 'process table'
end
This way any table can be selectively set up for processing or not without
touching the system tables and you have a better chance of not getting
broken with succesive releases of SQL Server.

Craig

[Damien]

Thanks, I'll look into that. I've not used extended properties before
(then again, we've only just upgraded from 7 (as in, last weekend), was
it available in 7?)

[Craig Kelly]

"Damien" wrote:

<snip>

was it available in 7?

<snip>

http://www.microsoft.com/sql/techinf...properties.asp

According to this link, no (the page above also contains links to articles
on using extended properties, although I didn't follow them to see if they
were still good). It's been a while since we upgraded from 7 to 2K, but
that also seems sync with my memory.

Craig