Marking a table as a System Table

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."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|||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?)|||"Damien" wrote:

<snip>
> was it available in 7?

<snip
http://www.microsoft.com/sql/techin...dproperties.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.

Craigsql