如何检查 SQL Server 表是否为系统表

Question

使用存储过程 sp_msforeachtable 可以对数据库中的所有表执行脚本。

但是，我想要排除一些系统表。直觉上，我会检查属性 IsSystemTable 或 IsMSShipped，但这些属性并不像我期望的那样工作——例如，我有一个名为 __RefactorLog 的表：

但当我查询这是否为系统或 MS Shipped 表时，SQL Server 报告没有我的表是系统表：

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

并且

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

当我查看表的属性（在SSMS内部）时，该表被标记为系统对象。然而像IsSystemObject这样的对象属性并不存在（据我所知）。

除了对象属性之外，我如何检查一个表是否为系统对象？SSMS如何检查表是否为系统对象？

- vstrien

---

尽管这个问题看起来像是https://dev59.com/vU7Sa4cB1Zd3GeqP8Phx，但它并不是重复的，因为自从SQL Server 2000以来检查对象属性的方式已经改变了，我正在询问SSMS检查它的方法。 - vstrien

当打开“系统表”文件夹时，您可以随时对服务器运行SQL Server分析器，并查看其正在运行的SQL。 - Damien_The_Unbeliever

Answer 1

当打开对象资源管理器中的“系统表”文件夹时，管理工具2008似乎会运行一些相当丑陋的代码，其中关键部分似乎是：

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]

（在此处，tbl 是 sys.tables 的别名）

因此，似乎这是一个组合——要么从 sys.tables 中使用 is_ms_shipped 为1，或设置了特定的扩展属性。

- Damien_The_Unbeliever

Answer 2

与SSMS所建议的相反，__refactorlog是一个用户表。它在部署期间用于跟踪模式更改，这些更改无法从当前数据库状态中推断出来，例如重命名表。

如果您的所有其他用户表都在自定义（非dbo）架构中，则可以使用isMSshipped/isSystemTable属性和架构名称的组合来决定表是否适用于您的脚本。

- S Koppenol

Answer 3

过去我一直认为，在sys.objects表中，列is_ms_shipped指示对象是否为系统对象。（此列被其他系统表（如sys.tables）继承。）

该标志可以由存储过程sp_ms_markSystemObject设置。然而，这是一个未记录的过程，不受Microsoft支持，我认为我们不应该知道它，所以我没有告诉你。

- Philip Kelley

Answer 4

我有什么遗漏吗？

然而，有一些系统表我想要排除在外。

至少在 SQL Server 2008 上，sp_MSforeachtable 已经排除了系统表，正如它的摘录所示：

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

- AakashM

---

我不知道你是否遗漏了什么，但当我以我的ETL用户身份登录并执行给定的查询时，查询还尝试处理__RefactorLog表（SSMS将其指定为“系统对象”）。 - vstrien

从前，我偶然发现企业管理器(SQL 2000)硬编码将某些表视为系统表，尽管SQL本身没有将它们标记为系统表。(这是图表。)他们可能仍在SSMS中使用此类技巧... - Philip Kelley