List schema name and owners in SQL Server 2012

Question

I need to list all the table, column, schema and the owner names in all of the databases and I have written the code below.

I am not certain where to get the schema owners details to add to my query.

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]

Can anyone help with this?

Answer 1

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

USER_NAME(s.principal_id) AS Schema_Owner

In another way, if you need 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

 Willing to Learn SQL? Come and join the Microsoft SQL Server Database certifications and training to gain more knowledge.