patternsqlMinor
View Default value for User Defined Table Type columns in SQL Server
Viewed 0 times
columnstableusersqlviewvaluetypedefaultforserver
Problem
I have a user defined table type in SQL Server 2014 that I need to drop and recreate. However, if I use the script from SSMS, the default value constraints that are on some of the columns, will not be created.
I also have problems viewing the default constraint, so I do not what the default value should be.
Is there a way to view what those default value constraints contain?
)
GO
Above is the definition as it is created when generating script in SSMS. As you can see, the default values are not in the script.
I also have problems viewing the default constraint, so I do not what the default value should be.
Is there a way to view what those default value constraints contain?
CREATE TYPE [Logging].[LogElements] AS TABLE(
[LogMessage] [nvarchar](MAX) NOT NULL,
[MessageTypeId] [smallint] NOT NULL,
[Origin] [varchar](50) NOT NULL,
[JobRunId] [int] NULL,
[InvoiceId] [int] NULL,
[AccountlineId] [int] NULL,
[MSErrorCode] [bigint] NOT NULL,
[LogStatusId] [int] NOT NULL,
[LogUser] [varchar](50) NULL)
GO
Above is the definition as it is created when generating script in SSMS. As you can see, the default values are not in the script.
Solution
Find your user-defined table type:
In my output, the object was called
Now we can use a script similar to Ryan Cooper's answer, by plugging that
This is my example output, which is fairly trivial to convert into a T-SQL script for recreating them.
-- UDTT will have a 'TT prefix'
select * from sys.objects where type = 'TT' and name like '%LogElements%';In my output, the object was called
TT_LogElements_3A4CA8FD and had an object_id of 978102525.Now we can use a script similar to Ryan Cooper's answer, by plugging that
object_id into the WHERE clause, as the parent_object_id:SELECT o.name AS [Constraint Name],
cl.name AS [Column Name],
s.name AS [Data Type],
cl.max_length AS [Length],
c.text AS [Default Value]
FROM sys.syscomments c
INNER JOIN sys.objects o
ON c.id = o.object_id
INNER JOIN sys.columns cl
ON o.parent_object_id = cl.object_id
AND cl.default_object_id = o.object_id
INNER JOIN sys.systypes s
ON cl.system_type_id = s.xtype
WHERE o.type = 'D'
AND parent_object_id = 978102525;This is my example output, which is fairly trivial to convert into a T-SQL script for recreating them.
[Constraint Name] [Column Name] [Data Type] [Length] [Default Value]
------------------------------------------------------------------------------------
DF__TT_LogEle__LogSt__3B40CD36 LogStatusId int 4 ((0))
DF__TT_LogEle__LogUs__3C34F16F LogUser varchar 50 ('Test')Code Snippets
-- UDTT will have a 'TT prefix'
select * from sys.objects where type = 'TT' and name like '%LogElements%';SELECT o.name AS [Constraint Name],
cl.name AS [Column Name],
s.name AS [Data Type],
cl.max_length AS [Length],
c.text AS [Default Value]
FROM sys.syscomments c
INNER JOIN sys.objects o
ON c.id = o.object_id
INNER JOIN sys.columns cl
ON o.parent_object_id = cl.object_id
AND cl.default_object_id = o.object_id
INNER JOIN sys.systypes s
ON cl.system_type_id = s.xtype
WHERE o.type = 'D'
AND parent_object_id = 978102525;[Constraint Name] [Column Name] [Data Type] [Length] [Default Value]
------------------------------------------------------------------------------------
DF__TT_LogEle__LogSt__3B40CD36 LogStatusId int 4 ((0))
DF__TT_LogEle__LogUs__3C34F16F LogUser varchar 50 ('Test')Context
StackExchange Database Administrators Q#154507, answer score: 3
Revisions (0)
No revisions yet.