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]

score 45 · Accepted Answer · answered Aug 11 '14 at 08:31

45

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

answered Aug 11 '14 at 08:31

GarethD

68,045
10
83
123

1

Thanks for this . How would I get the table owner ? – user3844877 Aug 11 '14 at 08:34
4

Use the column `principal_id` in `sys.tables`, this is only populated where the owner is different to the schema owner. – GarethD Aug 11 '14 at 08:37

score 17 · Answer 2 · answered Aug 24 '15 at 19:39

17

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

SELECT schema_name, schema_owner
FROM information_schema.schemata

answered Aug 24 '15 at 19:39

Gili

86,244
97
390
689

List schema name and owners in sql server 2012

2 Answers2