HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

View Default value for User Defined Table Type columns in SQL Server

Submitted by: @import:stackexchange-dba··
0
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?

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:

-- 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.