How to find all the owners of entities contained in a specified schema?

Published 04 October 07 01:00 AM | SQL Master 
How to find all the owners of entities contained in a specified schema? 
USE <database_name>;
GO
SELECT 'OBJECT' AS entity_type
    ,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name
    ,name 
FROM sys.objects WHERE SCHEMA_NAME(schema_id) = '<schema_name>'
UNION 
SELECT 'TYPE' AS entity_type
    ,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) AS owner_name
    ,name 
FROM sys.types WHERE SCHEMA_NAME(schema_id) = '<schema_name>' 
UNION
SELECT 'XML SCHEMA COLLECTION' AS entity_type 
    ,COALESCE(USER_NAME(xsc.principal_id),USER_NAME(s.principal_id)) AS owner_name
    ,xsc.name 
FROM sys.xml_schema_collections AS xsc JOIN sys.schemas AS s
    ON s.schema_id = xsc.schema_id
WHERE s.name = '<schema_name>';
GO
Got this TSQL script during a problem resolution with CSS.

Comments

# Other SQL Server Blogs around the Web said on October 4, 2007 1:22 AM:

How to find all the owners of entities contained in a specified schema? USE &lt;database_name&gt;; GO

# SSQA.net - SqlServer-QA.net said on October 4, 2007 2:13 AM:

How to find all the owners of entities contained in a specified schema? USE &lt;database_name&gt;; GO

Anonymous comments are disabled

About SQL Master

**__________________________________** SQL Server MVP, Sr. DBA & industry expert. - Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it. It is also a power and you will gain by sharing it.

Search

Go

This Blog

«October 2007»
SMTWTFS
30123456
78910111213
14151617181920
21222324252627
28293031123
45678910

Syndication