Skip to content

Instantly share code, notes, and snippets.

@RobinHerbots
Last active August 24, 2020 12:50
Show Gist options
  • Save RobinHerbots/1e17c70ad7d88c3861461d9ee64eef5c to your computer and use it in GitHub Desktop.
Save RobinHerbots/1e17c70ad7d88c3861461d9ee64eef5c to your computer and use it in GitHub Desktop.
usp_CreateTypeFromTable.sql
CREATE PROCEDURE [dbo].[usp_CreateTypeFromTable] @TableName SYSNAME,
@TypeName SYSNAME
AS
BEGIN
DECLARE @sql NVARCHAR(MAX) = N'';
SELECT @sql = @sql + N',' + CHAR(13) + CHAR(10) + CHAR(9) +
QUOTENAME(c.name) + ' ' + s.name +
IIF(LOWER(s.name) LIKE '%char',
'(' +
IIF(c.max_length = -1, 'max', CONVERT(VARCHAR(12),
(c.max_length / (IIF(LOWER(LEFT(s.name, 1)) = N'n', 2, 1))))) + ')',
'')
-- need much more conditionals here for other data types
FROM sys.columns AS c
INNER JOIN sys.types AS s
ON c.system_type_id = s.system_type_id
AND c.user_type_id = s.user_type_id
WHERE c.[object_id] = OBJECT_ID(@TableName);
SELECT @sql = N'CREATE TYPE ' + @TypeName
+ ' AS TABLE ' + CHAR(13) + CHAR(10) + '(' + STUFF(@sql, 1, 1, '')
+ CHAR(13) + CHAR(10) + ');';
-- print @sql;
exec sys.sp_executesql @sql;
END
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment