3

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.

  • Please share the type definition in your question. – Randolph West Nov 7 '16 at 18:14
  • @RandolphWest Added now. Is that what you were looking for? – GHauan Nov 7 '16 at 18:30
  • Strange, it seems to work when I test that on a 2014 (12.0.4439.1 to be precise). – irimias Nov 8 '16 at 8:10
3

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')
| improve this answer | |
2

You can see the defaults and their definitions with this query:

select object_name(o.parent_object_id) as ObjectName
        , cl.name as ColumnName
        , o.name as DefaultName
        , c.text as DefaultDefintion
from sys.syscomments c
join sys.objects o on c.id = o.object_id
join sys.columns cl on cl.object_id = o.parent_object_id AND cl.default_object_id = o.object_id 
where o.type = 'D'

Edited to find user defined types:

    DECLARE @MyTable AS dbo.MyTableType; 

select object_name(o.parent_object_id) as ObjectName 
             , cl.name as ColumnName 
             , o.name as DefaultName 
             , c.text as DefaultDefintion
from tempdb.sys.syscomments c 
join tempdb.sys.objects o on c.id = o.object_id 
join tempdb.sys.columns cl on cl.object_id = o.parent_object_id AND cl.default_object_id = o.object_id 
where o.type = 'D'
| improve this answer | |
  • Thank you, but this does not seem to also show for user-defined-table-types. I cannot see the defaults from my user defined table type. – GHauan Nov 7 '16 at 18:33
  • This is pretty hacky, but works in my test. – Ryan Cooper Nov 7 '16 at 18:59
  • DECLARE @MyTable AS dbo.MyTableTable; select object_name(o.parent_object_id) as ObjectName , cl.name as ColumnName , o.name as DefaultName , c.text as DefaultDefintion , o.* from tempdb.sys.syscomments c join tempdb.sys.objects o on c.id = o.object_id join tempdb.sys.columns cl on cl.object_id = o.parent_object_id AND cl.default_object_id = o.object_id where o.type = 'D' – Ryan Cooper Nov 7 '16 at 18:59
  • Thanks again, Ryan. Marked Randolphs post as answer since it was a bit more easy to read (the results), but yours helped along. – GHauan Nov 15 '16 at 8:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.