How to Check Existence of User-Define Table Type in SQL Server 2008

How to check existence of user-define table type in SQL Server 2008?

You can look in sys.types or use TYPE_ID:

IF TYPE_ID(N'MyType') IS NULL ...

Just a precaution: using type_id won't verify that the type is a table type--just that a type by that name exists. Otherwise gbn's query is probably better.

Where I can check user-define table type in SQL Server 2008 in SSMS? and how can I insert new column into it?

User-defined types cannot be modified after they are created, because
changes could invalidate data in the tables or indexes. To modify a
type, you must either drop the type and then re-create it, or issue an
ALTER ASSEMBLY statement by using the WITH UNCHECKED DATA clause. For
more information, see
ALTER ASSEMBLY (Transact-SQL).

Points to take care while using user defined table type

1.Default values are not allowed.

2.Primary key must be a persisted column.

3.Check constraint can not be done on non persisted computed columns

4.Non clustered indexes are not allowed.

5.It can't be altered. You have to drop and recreate it.

T-SQL DROP TYPE IF EXISTS

Please try this, use type_id instead of object_id

IF type_id('[MySchema].[tProjectType]') IS NOT NULL
        DROP TYPE [MySchema].[tProjectType];

CREATE TYPE [MySchema].[tProjectType] AS TABLE
    (
        Id INT
        , IsPrivate BIT
        , IsPublic BIT
    );

Is it possible to get table type definitions from INFORMATION_SCHEMA?

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'
);

UDT existence in SQL Server table

This should do it:

SELECT c.[name] AS ColumnName, ct.[name] AS UDTName
FROM sys.tables t 
     JOIN sys.columns c ON t.object_id = c.object_id
     JOIN sys.types ct ON c.user_type_id = ct.user_type_id
WHERE t.[name] = 'YourTableName'
  AND ct.is_user_defined = 1;

Check if table exists in SQL Server

For queries like this it is always best to use an INFORMATION_SCHEMA view. These views are (mostly) standard across many different databases and rarely change from version to version.

To check if a table exists use:

IF (EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))
BEGIN
    --Do Stuff
END

Check if table exists and if it doesn't exist, create it in SQL Server 2008

Something like this

IF  NOT EXISTS (SELECT * FROM sys.objects 
WHERE object_id = OBJECT_ID(N'[dbo].[YourTable]') AND type in (N'U'))

BEGIN
CREATE TABLE [dbo].[YourTable](
    ....
    ....
    ....
) 

END

---

Related Topics