| How to obtain list of all schemas in SQL Server Database?
To retrieve a list of all schemas in the given Sql Server database. We use below different queries:-
(1)SELECT name FROM sys.schemas
SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
(2)SELECT * FROM INFORMATION_SCHEMA.SCHEMATA
(3)SELECT * FROM sysusers WHERE gid <> 0
(4)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';
(5)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
(6)SELECT s.name AS schema_name,u.name AS schema_owner
FROM sys.schemas s INNER JOIN sys.sysusers u ON u.uid = s.principal_id
ORDER BY s.name; | | |