SQL Server Performance Forum – Threads Archive

System Type object

Hi, I have one question related to the system object.I have new db created and as i know whenever new database get created SQL server itself create 20 system table for managing DB. but i am not sure why its create 30 SP with Type as system. Do it own by system user or any one. What is difference between user object and system object. and how can i create system object. Please provide your inputs. Thanks and Regards
Ravi K

---

System Objects are needed of SQL Server use. why do you want to create system objects

---

To "create" a system object google on the use of the undocumented "sp_MS_marksystemobject" procedure. And note, that undocumented is equal to unsupported. Mostly for a very good reason. So, bottomline, if I were you, I wouldn’t do it. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

Whenever you create tables, procedures or functions, SQL Server records those in System tables.
No need to update them.
Madhivanan Failing to plan is Planning to fail

---

update sysobjects set status = status | 0xC0000000
where id = object_id(‘Name of the Object’) will also do the same
I think that sp also doing the same

---

Dinesh, ad hoc updates on the system tables are usually not allowed. You need to Reconfigure the server to allow this. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

Oh! yes sorry couldn’t mention thankx frank [<img src=’/community/emoticons/emotion-1.gif’ alt=’‘ />]

---

It’s it very late in your place? You live online? [<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />]<br /><br />–<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />

---

My first thought was: SQL Server uses the "model" database as a template for any new database. So if you have added any objects to the "model" database, then they will automatically appear in your new database. Then I tested, and found that I got a series of stored procedures named dt_xxxxxxxxxx, and they do not exist in the model database, but in master.

---

I think dt_XXX is for version control like Sourcesafe. Am I right on this? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)

---

Yes, the procedure names do suggest something along those lines. Not sure we actually use that software on our server …

---

These are stored procedures that are created by Visual Studio (interdev in particular) to be able to use source control with the stored procedures. If you are using interdev sourcecontrol on you Sps then you should have a process in your release mechanism that eliminates these. Also If your prod. server has interdev server on it and you connect to it with interdev then they will keep coming back http://beta.experts-exchange.com/Databases/Microsoft_SQL_Server/Q_10314309.html

---

Thanks for the suggestions. I’ll check with our systems person (we’re not exactly overorganized).

---

Ravi Could you please convey the information about why do you want to mast the object(s) as system. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

]]>