0 votes
1 view
in SQL by (6.1k points)

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?

1 Answer

0 votes
by (12.7k points)
edited by

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 SQLCome and join the Microsoft SQL Server Database certifications and training to gain more knowledge.

Related questions

0 votes
1 answer
0 votes
1 answer
asked Dec 30, 2020 in SQL by Appu (6.1k points)
0 votes
1 answer
asked Dec 26, 2020 in SQL by Appu (6.1k points)
0 votes
1 answer
Welcome to Intellipaat Community. Get your technical queries answered by top developers !