How do we differentiate between USer tables and System Tables in SQL Server

Thanks

Plz Reply ASAP

Best Regards

Anil Mahadev

Software Dev 2 Da Max

what do you mean by differentiate between them???
you can observe that system table names starts with "sys"


Ahmed Ali
Software Developer

Hello,

If you select from the sysobjects table, the field xtype can be used to make that determination. This char(2) field uses the following table values:

S = System table
U = User table
V = View

This is apart from the fact that most system tables start with sys, other than the dtproperties table.

I don't know if you can differentiate between these two types using the INFORMATION_SCHEMA.TABLES or another of the INFORMATION_SCHEMA views.

Brian

Dear Brian,

Thanks 4 the help given.

My question was actually,

Can we change a sys table to a user table in a database.

Thanks

Anil Mahadev

Software Dev 2 Da Max

first:
i am asking what is the user table advantages so i need to change the sys table to a user table.
secoded:
if you change for example the name of system table all sys procedure and views will fail.

you can get the system tables data after inserting it in separate temperory table like follows:

select * into #table1
from dbo.syscolumns


Ahmed Ali
Software Developer

Hello,

You have to look at some of the change object procedures for that. I don't believe so, because the system tables are needed by SQL Server. Why do you need to do this?

Brian