September 19, 2016 at 1:26 am
I have a script to create a database and its tables from an application, but the table columns are all custom data types. In SSMS I can go to 'ServerName|Databases|DatabaseName|Programmability|Types|User-Defined Data Types', right-click on each custom data type, and select 'Script User-Defined Data Type as|CREATE TO|Clipboard', and paste it into a script, one at a time. Repeat this a couple thousand times, and I should be done by 2019 or so...
Is there a faster way to copy just the user-defined data types (other than backing up and restoring the entire database)? I'm wondering if they're actually stored in a system database table somewhere that I can't find.
Thanks!
September 19, 2016 at 2:21 am
You could use Generate Scripts
In SSMS, right click on the database
Select Tasks -> Generate Scripts
From here you can select specific database objects
User-Defined Data Types is an option
Does that help?
- Damian
September 19, 2016 at 4:42 am
Yes, it helps. Sometimes you can't see the forest through the trees...
I was trying much too hard, and wrote the following script. It needs a little work for weird data types like image or xml, but this got me most of the way there. Now that you showed me the Generate Scripts option, I may never bother finishing it, but here's what I wrote if it helps anyone else. Change the table names at the bottom, execute it, and copy the result to another query editor window:
[font="Courier New"]
SELECT DISTINCT 'CREATE TYPE [dbo].[' + udt.name + '] FROM [' + sdt.name + ']' +
CASE
WHEN udt.collation_name IS NOT NULL AND udt.max_length >= 0 -- character types...
THEN '(' + CAST(udt.max_length/2 AS NVARCHAR) + ')'
WHEN udt.collation_name IS NOT NULL AND udt.max_length = -1 -- nvarchar(max)...
THEN '(max)'
WHEN sdt.system_type_id IN (165) AND udt.max_length = -1 -- varbinary(max)...
THEN '(max)'
WHEN sdt.system_type_id IN(60,106,108,122)
THEN '(' + CAST(c.precision AS NVARCHAR) -- numeric types...
+ ',' + CAST(c.scale AS NVARCHAR) + ')'
ELSE ''
END
+ ' NULL;'
FROM sys.columns c
INNER JOIN sys.types udt ON c.user_type_id = udt.user_type_id
INNER JOIN sys.types sdt ON udt.system_type_id = sdt.user_type_id
WHERE udt.is_user_defined = 1
AND c.object_id IN ( SELECT o.object_id
FROM sys.objects o
WHERE o.name IN ('TableName1'
, 'TableName2'
, 'TableName...'
, 'TableNameN')
AND o.type = 'U');
GO[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply