Sql Server : How to check if a user define table type exists using query (TSQL)
- Posted by Sqltimes
- On May 10, 2014
- 0 Comments
Quick one today
When we prepare SQL scripts to run in a production environment, we practice defensive coding mechanism. So, before we DROP an object, we check if it actually exists; Check if a function exists, before DROPping it.
Today’s question is how to do that for a user-defined table type?
See example below:
|
1
2
3
4
5
6
7
8
|
---- Query the sys.types--SELECT *FROM sys.typesWHERE is_table_type = 1AND name ='StoreLocationType'GO |
Now let’s use this in an actual example:
|
1
2
3
4
5
6
7
8
|
— * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
-- Step 1: Create new user defined table type : StoreLocationType-- * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * IF NOT EXISTS (SELECT * FROM sys.types WHERE is_table_type = 1 AND name ='StoreLocationType') CREATE TYPE dbo.StoreLocationType AS TABLE( LocationID BIGINT NOT NULL PRIMARY KEY CLUSTERED (LocationID ASC) WITH (IGNORE_DUP_KEY = OFF) )GO |
Types are different from other objects because they are not schema bound. So we need to use sys.types system view.
Hope this helps,
0 Comments