如何获取 Sql Server 数据库中所有模式的列表

How do I obtain a list of all schemas in a Sql Server database(如何获取 Sql Server 数据库中所有模式的列表)

问题描述

我想检索给定 Sql Server 数据库中所有模式的列表.使用 ADO.NET 架构检索 API，我得到了所有集合的列表，但没有架构"的集合.我可以遍历 'Tables'、'Procedures' 集合(以及其他如果需要)并获得唯一模式名称的列表，但没有更简单/更短的方法达到同样的效果?

I want to retrieve a list of all schemas in a given Sql Server database. Using the ADO.NET schema retrieval API, I get a list of all collections but there is no collection for 'Schemas'. I could traverse the 'Tables', 'Procedures' collections (and others if required) and obtain a list of unique schema names but isn't there a easier/shorter way of achieving the same result?

示例:对于标准 'AdventureWorks' 数据库，我想获得以下列表 - dbo,HumanResources,Person,Production,Purchasing,Sales(我已经省略了其他标准模式名称，如 db_accessadmin、db_datareader 等)

Example: For the standard 'AdventureWorks' database I would like to obtain the following list - dbo,HumanResources,Person,Production,Purchasing,Sales (I've omitted the other standard schem names like db_accessadmin,db_datareader etc)

我可以通过查询系统视图来获取架构列表 - INFORMATION_SCHEMA.SCHEMATA，但我更喜欢使用架构 API 作为首选.

I can get the list of schemas by querying the system view - INFORMATION_SCHEMA.SCHEMATA but would prefer using the schema API as first choice.

推荐答案

对于 2005 年及以后的版本，这些都将满足您的需求.

For 2005 and later, these will both give what you're looking for.

SELECT name FROM sys.schemas
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA

对于 2000，这将给出 instance 中的 数据库 列表.

For 2000, this will give a list of the databases in the instance.

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA

这就是@Adrift 的回答中提到的向后不兼容".

That's the "backward incompatability" noted in @Adrift's answer.

在 SQL Server 2000(及更低版本)中，实际上并没有这样的模式"，尽管您可以以类似的方式将角色用作命名空间.在这种情况下，这可能是最接近的等价物.

In SQL Server 2000 (and lower), there aren't really "schemas" as such, although you can use roles as namespaces in a similar way. In that case, this may be the closest equivalent.

SELECT * FROM sysusers WHERE gid <> 0

这篇关于如何获取 Sql Server 数据库中所有模式的列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！