How to List User Defined Types in a SQL Server Database

How do I list user defined types in a SQL Server database?

Types and UDTs don't appear in sys.objects.
You should be able to get what you're looking for with the following:

select * from sys.types
where is_user_defined = 1

Query all user defined types from Sql Server?

In SQL Server 2005 (and above) you can do this

SELECT * FROM sys.Types WHERE is_user_defined = 1

If you are on SQL Server 2000, you can use SysTypes. I think the query is as follows:

SELECT * FROM SysTypes WHERE xusertype > 256

How do I list user defined types in a HANA SQL Database?

As table types are the only user-definable types in SAP HANA you find those in the TABLES system table:

SELECT 
       * 
FROM 
     tables 
WHERE 
      is_user_defined_type ='TRUE';

cheers,
Lars

Get Column information of a user defined Table Type

I have just written and tested this on SQL Server 2014, hopefully should work with all versions.

Select t.name   [TableTypeName]
      ,SCHEMA_NAME(t.schema_id)  [SchemaName]
      ,c.name   [Column Name]
      ,y.name   [Data Type]
      ,c.max_length
      ,c.precision
      ,c.is_identity
      ,c.is_nullable
From sys.table_types t
Inner join sys.columns c on c.object_id = t.type_table_object_id
Inner join sys.types y ON y.system_type_id = c.system_type_id
WHERE t.is_user_defined = 1
  AND t.is_table_type = 1

Copying user-defined types from one database to another

Right click on the database in SQL Server Management Studio, choose Tasks > Generate Scripts.... Then check user defined data types and table types. Click Next etc up until Finish.

Once you have the script generated, execute it on the database you want to create the types in.

Identifying all stored procedures and tables using a User-Defined Data Type in T-Sql

Refer to the article below on how to properly search for a string in a stored procedure definition:

Search text in stored procedure in SQL Server

As I posted in a comment above, ROUTINE_DEFINITION is an NVARCHAR(4000) and longer stored procedures have their definition truncated.

Your second method is not bad, it gets it done but yes your first is more correct.

SQL, Get a list of all the custom data types

Oracle has a data dictionary view for almost everything. You can select from
ALL_TYPES and ALL_TYPE_ATTRS. The latter shows the owner of each type.

Postgres does not have the same functionality so you can use this query from here

SELECT      n.nspname as schema, t.typname as type 
FROM        pg_type t 
LEFT JOIN   pg_catalog.pg_namespace n ON n.oid = t.typnamespace 
WHERE       (t.typrelid = 0 OR (SELECT c.relkind = 'c' FROM pg_catalog.pg_class c WHERE c.oid = t.typrelid)) 
AND     NOT EXISTS(SELECT 1 FROM pg_catalog.pg_type el WHERE el.oid = t.typelem AND el.typarray = t.oid)
AND     n.nspname NOT IN ('pg_catalog', 'information_schema')

---

Related Topics