Hoe krijg ik een lijst van alle schema’s in een Sql Server-database

Ik wil een lijst van alle schema’s in een bepaalde Sql Server-database ophalen. Met behulp van de ADO.NETschema-ophaal-API krijg ik een lijst van alle verzamelingen, maar er is geen verzameling voor ‘Schema’s’.
Ik zou de verzamelingen 'Tables', 'Procedures'(en andere indien nodig) kunnen doorlopen en een lijst met unieke schemanamen verkrijgen, maar is er geen eenvoudigere/kortere manier hetzelfde resultaat te bereiken?

Voorbeeld: voor de standaard 'AdventureWorks'-database zou ik graag de volgende lijst willen verkrijgen: dbo,HumanResources,Person,Production,Purchasing,Sales(ik heb heeft de andere standaardschemanamen weggelaten, zoals db_accessadmin,db_datareaderetc)

Bewerken: ik kan de lijst met schema’s krijgen door de systeemweergave te doorzoeken – INFORMATION_SCHEMA.SCHEMATAmaar zou liever de schema-API als eerste keuze gebruiken.


Antwoord 1, autoriteit 100%

Voor 2005 en later bieden deze beide wat u zoekt.

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

Voor 2000 geeft dit een lijst van de databasesin de instantie.

SELECT * FROM INFORMATION_SCHEMA.SCHEMATA

Dat is de “achterwaartse incompatibiliteit” die wordt opgemerkt in het antwoord van @Adrift.

In SQL Server 2000 (en lager) zijn er niet echt “schema’s” als zodanig, hoewel je rollen op een vergelijkbare manier als naamruimten kunt gebruiken. In dat geval kan dit het dichtste equivalent zijn.

SELECT * FROM sysusers WHERE gid <> 0

Antwoord 2, autoriteit 16%

Probeer deze zoekopdracht hier:

SELECT * FROM sys.schemas

Dit geeft je de naam en schema_id voor alle gedefinieerde schema’s in de database waarin je dit uitvoert.

Ik weet niet echt wat je bedoelt met het opvragen van de “schema-API” – deze sys.-catalogusweergaven (in het sys-schema) zijn de beste keuze voor alle systeeminformatie over databases en objecten in die databases.


Antwoord 3, autoriteit 7%

SELECT s.name + '.' + ao.name
       , s.name
FROM sys.all_objects ao
INNER JOIN sys.schemas s ON s.schema_id = ao.schema_id
WHERE ao.type='u';

Antwoord 4, autoriteit 6%

U kunt ook de weergave INFORMATION_SCHEMA.SCHEMATA opvragen:

SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA

Ik denk dat het aan te raden is om de INFORMATION_SCHEMA views op te vragen, omdat ze je beschermen tegen wijzigingen aan de onderliggende sys-tabellen. Uit de SQL Server 2008 R2 Help:

Informatieschemaweergaven bieden een
intern, systeemtabelonafhankelijk
weergave van de SQL Server-metadata.
Informatieschemaweergaven inschakelen
applicaties om correct te werken
hoewel er belangrijke veranderingen zijn geweest
gemaakt naar de onderliggende systeemtabellen.
De informatieschemaweergaven inbegrepen
in SQL Server voldoen aan de ISO
standaarddefinitie voor de
INFORMATION_SCHEMA.

Ironisch genoeg wordt dit onmiddellijk voorafgegaan door deze opmerking:

Er zijn enkele wijzigingen aangebracht in de
informatieschemaweergaven die breken
achterwaartse compatibiliteit. Deze veranderingen
worden beschreven in de onderwerpen voor de
specifieke weergaven.


Antwoord 5, autoriteit 4%

Als u Sql Server Management Studio gebruikt, kunt u een lijst van alle schema’s verkrijgen, uw eigen schema maken of een bestaand schema verwijderen door te bladeren naar:

Databases – [Uw database] – Beveiliging – Schema’s

[


Antwoord 6, autoriteit 2%

U kunt ook de volgende query gebruiken om schema’s voor een specifieke databasegebruiker op te halen:

select s.schema_id, s.name as schema_name
from sys.schemas s
inner join sys.sysusers u on u.uid = s.principal_id
where u.name='DataBaseUserUserName'
order by s.name

Other episodes