0

I am working with SQL Server 2008 R2 and SQL Server 2016.

I have bunch of User-Defined Data Types but they dont show in sys.all_objects. What is the true catalog view which can return all user-defined objects?

Thanks

Syed
  • 27
  • 8
  • 1
    Types aren't *objects*. You'll find them in `sys.types` – Panagiotis Kanavos Oct 31 '18 at 16:14
  • agree but i am writing a query where I have to treat them as one for join purpose – Syed Oct 31 '18 at 16:17
  • 1
    In this case you'll have to change the query because they just aren't objects. You can't retrieve them from `sys.objects`. They don't have an OBJECT_ID. Change the query and query the correct table – Panagiotis Kanavos Oct 31 '18 at 16:18
  • Possible duplicate of [How do I list user defined types in a SQL Server database?](https://stackoverflow.com/questions/54482/how-do-i-list-user-defined-types-in-a-sql-server-database) – CR241 Oct 31 '18 at 17:27

1 Answers1

2

Types are not objects that would show up in the objects table. You can use the types table:

select * 
from sys.types
WHERE is_user_defined = 1

or you can use this bigger query from the MS docs to return all your objects, types, and schema collections: 

SELECT 'OBJECT' AS entity_type  
    ,USER_NAME(OBJECTPROPERTY(object_id, 'OwnerId')) AS owner_name  
    ,name   
FROM sys.objects 
UNION   
SELECT 'TYPE' AS entity_type  
    ,USER_NAME(TYPEPROPERTY(SCHEMA_NAME(schema_id) + '.' + name, 'OwnerId')) AS owner_name  
    ,name   
FROM sys.types   
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  

GO
dfundako
  • 8,022
  • 3
  • 18
  • 34