Good script.

Here is an improved version that:

- fixes the length = -1 problem for MAX.

- PRINTs the output to the Messages tab to make it easier to copy and past to a new query or to a file.

- adds a USE statement so it is obviouse where the data type came from.

- add the nullablity of the column.

DECLARE

@Stmt VARCHAR(2048)

,@lf CHAR(2) = CHAR(13) + CHAR(10)

DECLARE tmp_cr CURSOR

FOR

SELECT

'USE ' + DB_NAME() + @lf + 'GO' + @lf + 'IF EXISTS(SELECT 1 ' + @lf

+ 'FROM sys.systypes ' + @lf + 'where Name = ''' + st.name + ''')'

+ @lf + ' BEGIN' + @lf + 'DROP TYPE ' + st.name + @lf + @lf

+ 'CREATE TYPE ' + st.name + ' AS TABLE' + @lf + '('

+ STUFF((SELECT

',' + sc.Name + ' ' + st1.Name + ''

+ CASE WHEN St1.Name IN ('CHAR', 'VARCHAR', 'NVARCHAR')

THEN CASE WHEN sc.xprec = 0

AND SC.xscale = 0

THEN '('

+ CASE WHEN SC.length = -1

THEN 'max'

ELSE CAST(SC.length AS NVARCHAR(100))

END + ') '

ELSE '('

+ CAST(sc.xprec AS NVARCHAR(100))

+ ','

+ CAST(sc.xscale AS NVARCHAR(100))

+ ') '

END

ELSE ' '

END + CASE SC.isnullable

WHEN 1 THEN 'null '

ELSE 'not null '

END + @lf

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, 2, '') + ')' + @lf + ' END' + @lf

+ 'GO' AS Stmt

FROM

sys.table_types st

OPEN tmp_cr

WHILE 1 = 1

BEGIN

FETCH NEXT FROM tmp_cr INTO @Stmt

IF @@FETCH_STATUS <> 0

BREAK

PRINT @Stmt

END

CLOSE tmp_cr

DEALLOCATE tmp_cr

The results from running it on msdb look like this:

USE msdb

GO

IF EXISTS(SELECT 1

FROM sys.systypes

where Name = 'syspolicy_target_filters_type')

BEGIN

DROP TYPE syspolicy_target_filters_type

CREATE TYPE syspolicy_target_filters_type AS TABLE

(target_filter_id int null

,policy_id int null

,type sysname not null

,filter nvarchar(max) not null

,type_skeleton sysname not null

)

END

GO