Technical Article

Get the defination of all table type in database

,

This script will generate the Drop and create script for all user define table type.

SELECT 
'IF EXISTS(SELECT TOP 1 NULL FROM sys.systypes where Name = '''+st.name+''')'+CHAR(13)+CHAR(10)+
'DROP TYPE '+st.name+';'
+CHAR(13)+CHAR(10)+
+CHAR(13)+CHAR(10)+
+CHAR(13)+CHAR(10)+
'CREATE TYPE '+st.name + ' AS TABLE'+
CHAR(13)+CHAR(10)+
'('+
STUFF((
SELECT ','+
sc.Name +' ' +st1.Name+' '+
CASE WHEN St1.Name  IN ('CHAR','VARCHAR','NVARCHAR') 
 THEN CASEWHEN sc.xprec = 0 AND SC.xscale = 0  
THEN '('+CAST(SC.length as NVARCHAR(100))+')'
ELSE '('+CAST(sc.xprec as NVARCHAR(100))+','+CAST(sc.xscale as NVARCHAR(100))+')'
  END
  ELSE ''
  END+CHAR(13)+CHAR(10)
FROM sys.syscolumns SC
INNER JOIN sys.systypes st1
ON st1.xtype = sc.xtype
AND st1.xusertype = sc.xusertype
where st.type_table_object_id = sc.id
FOR XML  PATH(''),type
).value('.', 'nvarchar(max)'),1,1,'')+')'
 from sys.table_types  st

Rate

1.25 (4)

Share

Share

Rate

1.25 (4)