Follow SQLMaster on Twitter
Welcome to SqlServer-QA.net Sign in | Help

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

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.
Published Thursday, October 04, 2007 1:00 AM by SQL Master

Comments

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

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

Thursday, October 04, 2007 1:22 AM by Other SQL Server Blogs around the Web

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

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

Thursday, October 04, 2007 2:13 AM by SSQA.net - SqlServer-QA.net
Anonymous comments are disabled