How to check if SQL Server tables are system tables

Question

Using the sp_msforeachtable stored procedure, you can execute a script for all tables in the database.

However, there are system tables that I would like to exclude from this. Instinctively, I would check the IsSystemTable or IsMSShipped . They do not work, as I expect, for example, I have a table called __RefactorLog :

But when I query if it is a system or MS-sent table, SQL Server does not report any of my system table tables:

 exec (N'EXEC Database..sp_msforeachtable "PRINT ''? = '' + CAST(ObjectProperty(Object_ID(''?''), ''IsSystemTable'') AS VARCHAR(MAX))"') AS LOGIN = 'MyETLUser' -- Results of IsSystemTable: [dbo].[__RefactorLog] = 0 [schema].[myUserTable] = 0 

and

 exec (N'EXEC Database..sp_msforeachtable "PRINT ''? = '' + CAST(ObjectProperty(Object_ID(''?''), ''IsMSShipped'') AS VARCHAR(MAX))"') AS LOGIN = 'MyETLUser' -- Results of IsMSShipped: [dbo].[__RefactorLog] = 0 [schema].[myUserTable] = 0 

When I look at the properties of a table (inside SSMS), the table is marked as a system object. An object property, such as IsSystemObject , does not exist (AFAIK).

How to check if a table is a system object other than an object property? How does SSMS check if a table is a system object?

Answer 1

Management Studio 2008 seems to run the rather ugly following code when opening the System Tables folder in the object explorer, the key bit looks like this:

 CAST( case when tbl.is_ms_shipped = 1 then 1 when ( select major_id from sys.extended_properties where major_id = tbl.object_id and minor_id = 0 and class = 1 and name = N''microsoft_database_tools_support'') is not null then 1 else 0 end AS bit) AS [IsSystemObject] 

(Where tbl is an alias for sys.tables )

Thus, it seems that this combination is either is_ms_shipped from sys.tables equal to 1, or has a specific extended set of properties.

Answer 2

In the past, I have worked on the assumption that in the sys.objects table, the is_ms_shipped column indicates whether or not a system object is. (This column is inherited by other system tables, such as sys.tables.)

This flag can be set by sp_ms_markSystemObject. This, however, is an undocumented procedure, not supported by Microsoft, I don’t think we should know about it, so I didn’t tell you about it.

Answer 3

__ refactorlog, unlike what SSMS offers, is a user table. It is used during deployment to track schema changes that cannot be deduced from the current state of the database, such as renaming a table.

If all your other user tables are in a custom (non-dbo) schema, you can use a combination of the isMSshipped / isSystemTable attributes and the schema name to decide if the table is "in scope" for your script.

Answer 4

Am I missing something?

However, there are system tables that I would like to exclude from this

At least on SQL Server 2008, sp_MSforeachtable already excludes system tables, as this excerpt shows:

 + N' where OBJECTPROPERTY(o.id, N''IsUserTable'') = 1 ' + N' and o.category & ' + @mscat + N' = 0 '