How to check if SQL Server Tables are System Tables
sql-server, ssms, t-sql
Solution
Management studio 2008 seems to run some quite ugly following code when opening the "System Tables" folder in the object explorer, the key bit seems to be:
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`)
So it seems that it's a combination - either `is_ms_shipped` from `sys.tables` being 1, or having a particular extended property set.
Problem
Using the stored procedure `sp_msforeachtable` it's possible to execute a script for all tables in a database. However, there are system tables which I'd like to exclude from that. Instinctively, I would check the properties `IsSystemTable` or `IsMSShipped`. These don't work like I expect - I have for example a table called `__RefactorLog`: But when I query if this is a system or MS Shipped table, SQL Server reports none of my tables are system 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 into the properties of the table (inside SSMS), the table is marked as a system object. An object property like `IsSystemObject` doesn't exist though (AFAIK). How do I check if a table is a system object, apart from the object property? How does SSMS check if a table is a system object?