CODEWITHSUNDEEP
sql-serversql-server-2008
user1309871
user1309871

Reputation: 257

How to create table in SYS schema in SQL Server?

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 ?

Upvotes: 4

Views: 6829

Answers (3)

TorbjornS
TorbjornS

Reputation: 31

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 ?

Upvotes: 3

Tim Lehner
Tim Lehner

Reputation: 15251

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.

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?

Upvotes: 4

Ben Thul
Ben Thul

Reputation: 32667

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.

Upvotes: 2

Related Questions