Can one create table in sys schema SQL Server 2008 ? I know it is possible to mark table as system, but not change the schema. Any hacks for it ?
10
-
12Why on earth would you want to do this?Bridge– Bridge2013-06-07 14:32:17 +00:00Commented Jun 7, 2013 at 14:32
-
5This is really not a good idea. If you tell us WHY you want to do this we may be able to redirect you.JNK– JNK2013-06-07 14:33:05 +00:00Commented Jun 7, 2013 at 14:33
-
3I don't even know. It's never crossed my mind probably because this sounds like the worst idea ever.Zane– Zane2013-06-07 14:34:11 +00:00Commented Jun 7, 2013 at 14:34
-
2You may be able to change an objects schema via directly poking the system tables via the DAC, but as others have commented, I can see nothing good that could be achieved by doing thisDamien_The_Unbeliever– Damien_The_Unbeliever2013-06-07 14:38:56 +00:00Commented Jun 7, 2013 at 14:38
-
3As I noted in Tim Lehner's answer below, sucessfully doing this would void your employer's MS support contract and product warranty. So no responsible professional is going to tell you how to do it. And especially not in a public forum.RBarryYoung– RBarryYoung2013-06-07 18:37:04 +00:00Commented Jun 7, 2013 at 18:37
|
Show 5 more comments
3 Answers
You cannot add your own objects to the sys schema.
User-defined objects cannot be created in the sys schema.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
If that's not convincing enough, consider that system objects are stored in the resource database, which is read-only:
The Resource database is a read-only database that contains all the system objects that are included with SQL Server. SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database. The Resource database does not contain user data or user metadata.
- Source: msdn - Resource Database
If there does exist some undocumented method to do this, as others have stated, it's probably not in your best interest (or that of your software and team) to do so. I would suggest stepping back and attacking your problem from another angle. Perhaps some sort of mock setup?
1 Comment
RBarryYoung
Actually, there is a way to do it, but since it would void the MS support contract and product warranty of anyone who did it, I am not going to post it publicly, and I'm certainly not going to instruct the OP in how to shoot his own foot off. (I know you wouldn't do that, Tim, but everyone else who reads this is another story).
This works for me:
I use sql server 2012. Not sure if it works in older versions.
EXEC sp_addextendedproperty N'microsoft_database_tools_support', N'migration log', N'schema', N'dbo', N'table', N'__MigrationHistory'
Any suggestions ?
1 Comment
Karsten
Just what I needed. Works with 2005
You've asked for a way to mark an object as a system object. There's an undocumented procedure called sp_ms_marksystemobject that does exactly that. There are some legitimate uses for this, but for the most part what you're trying to do is better accomplished without resulting to this. In short, weigh your options carefully before employing this. You've been forewarned.
1 Comment
Martin Smith
Think you misread the question. They say "I know it is possible to mark table as system"