gpt4 book ai didi

sql-server - 查询Sys.Tables时如何排除系统表?

转载 作者:行者123 更新时间:2023-12-02 22:04:14 26 4
gpt4 key购买 nike

我正在运行此 SQL:

SELECT S.name as Owner, T.name as TableName FROM  sys.tables AS T
JOIN sys.schemas AS S ON S.schema_id = T.schema_id

结果是:

Owner   TableName
------------------------
dbo Person
dbo Customer
dbo sysdiagrams

sysdiagrams 是一个系统表,但显示在结果中。

更新:感谢大家的回答和评论,我正在使用 Nate Bolamvmvadivel 答案:

SELECT S.name as Owner, T.name as TableName 
FROM
sys.tables AS T
INNER JOIN sys.schemas AS S ON S.schema_id = T.schema_id
LEFT JOIN sys.extended_properties AS EP ON EP.major_id = T.[object_id]
WHERE
T.is_ms_shipped = 0 AND
(EP.class_desc IS NULL OR (EP.class_desc <>'OBJECT_OR_COLUMN' AND
EP.[name] <> 'microsoft_database_tools_support'))

最佳答案

SSMS 使用扩展属性将 sysdiagrams 表标记为一种伪系统表。

试试这个:

SELECT S.name as Owner, T.name as TableName FROM  sys.tables AS T
INNER JOIN sys.schemas AS S ON S.schema_id = T.schema_id
LEFT JOIN sys.extended_properties AS EP ON EP.major_id = T.[object_id]
WHERE (EP.class_desc IS NULL
OR (EP.class_desc <> 'OBJECT_OR_COLUMN'
AND EP.[name] <> 'microsoft_database_tools_support'))

关于sql-server - 查询Sys.Tables时如何排除系统表?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/8774928/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com