Home > DeveloperSection > Forums > How can I create a SQL Server system table
Chintoo Semi

Total Post:135

Points:947
Posted on    February-18-2015 7:28 AM

 ASP.Net Code First  Entity Framework  TSQl 
Ratings:


 2 Reply(s)
 525  View(s)
Rate this:
I am developing a site that uses Entity Framework code first with migrations. After each major release, I take a copy of the production data and set it up in my development environment. I've found that in order to use a production data copy with migrations, it is necessary to copy the __MigrationHistory table from the old development database image to the production database copy.

This works fine, except for one annoying detail: the _MigrationHistory table comes across as a user table instead of a system table. This is not a serious problem as long as I remember to filter it out of my database diffs. But I would prefer to have the new __MigrationHistory table be a system table, just as it is in a database that was created by EF migrations.

Is there a way I can create or copy a SQL Server table so that it appears to be a system table?


Goti Bandu

Total Post:119

Points:835
Posted on    February-18-2015 7:38 AM

Use the following command change your table to system object. So it will then appear in System Objects.

       EXEC sys.sp_MS_marksystemobject _MigrationHistory

Kamlakar Singh
Kamlakar Singh

Total Post:194

Points:1396
Posted on    February-24-2015 2:27 AM

SELECT name = convert(char(30),o.name), rows, dpages,o.id, type
FROM sysindexes i JOIN sysobjects o ON o.id = i.id
WHERE o.id < 100 and (indid = 0 or indid =1)

Don't want to miss updates? Please click the below button!

Follow MindStick