Number of objects related to schema in SQL Server

Number of objects related the schema in a particular database of SQL Server

Every object in the database should belong to a particular schema in the database of SQL Server.

Below query will relate the Logins/ username/ Schema/ Default database


select SL.loginname [Login Name],SU.name as [User Name],SL.dbname as [Default DB]
,SS.name as [Schema Name]
from sys.syslogins SL join sysusers SU
on suser_sname(SL.SID)=suser_sname(SU.SID)
left outer join sys.schemas SS
on SU.UID=SS.Principal_id

Below query will provide the number of objects related to the schema:


select distinct schema_id, schema_name(schema_id) as [schema name],count(1) as [objects of Schema] from sys.objects
group by schema_id
order by schema_id

Hope the above helps

Thanks