List schema name and owners in sql server 2012

Question

I wanted to list all the table names, column names , schema names and owner in all databases and wrote the code below . I am not sure where to get the schema owners details to add to my query . Please help .

select  c.name as colomn_name , t.name as table_name , s.name as schema_name   from sys.columns c     inner join  sys.tables t     on c.object_id=t.object_id    INNER JOIN sys.schemas AS s  ON t.[schema_id] = s.[schema_id] 

Answer 1

The column principal_id in sys.schemas contains the ID of the schema owner, so to get the name you can simply use:

USER_NAME(s.principal_id) AS Schema_Owner 

Alternatively, if you want more information you can join to sys.sysusers:

SELECT  s.Name, u.* FROM    sys.schemas s         INNER JOIN sys.sysusers u             ON u.uid = s.principal_id 

Answer 2

Here is a more portable solution that works under SQL Server:

SELECT schema_name, schema_owner FROM information_schema.schemata