Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Is it possible to get table type definitions from INFORMATION_SCHEMA?

Tags:

I know that user defined types are stored in information_schema.domains, but is it possible to get the definitions for types which are of type table type?

To be clear I am looking for the actual table type definition:

e.g. I want to get the column definitions for myTableType

CREATE TYPE myTableType AS TABLE(
     Id INT,
     SomeValue NVARCHAR(20)
);
like image 861
Dustin Kingen Avatar asked Dec 10 '12 17:12

Dustin Kingen


People also ask

How can check user defined table type in SQL Server?

Once you connect to a database in SSMS, you can view these data types by navigating to Programmability-> Types->System Data Types.

How do I find the definition table in SQL?

Using SQL Server Management Studio In Object Explorer, select the table for which you want to show properties. Right-click the table and choose Properties from the shortcut menu. For more information, see Table Properties - SSMS.

What is the use of INFORMATION_SCHEMA in SQL Server?

INFORMATION_SCHEMA provides access to database metadata, information about the MySQL server such as the name of a database or table, the data type of a column, or access privileges. Other terms that are sometimes used for this information are data dictionary and system catalog.

What does the following command do SELECT * from INFORMATION_SCHEMA tables?

It provides the read-only access to details related to databases and their objects (tables, constraints, procedures, views…) stored on the server. You could easily use this data to: Check what's on the server and/or in the database.


1 Answers

To get the list of columns for a user-defined table type, run this. You'll need to substitute your table name for some_table_type:

SELECT *
FROM sys.columns
WHERE object_id IN (
  SELECT type_table_object_id
  FROM sys.table_types
  WHERE name = 'some_table_type'
);
like image 109
Yuck Avatar answered Oct 04 '22 19:10

Yuck