主页 / dba / 问题 / 154507
Accepted
GHauan
Asked: 2016-11-08 10:01:23 +0800 CST

查看 SQL Server 中用户定义的表类型列的默认值

  • 772

我在 SQL Server 2014 中有一个用户定义的表类型，我需要删除并重新创建它。但是，如果我使用 SSMS 中的脚本，则不会创建某些列上的默认值约束。

我也有查看默认约束的问题，所以我不知道默认值应该是什么。

有没有办法查看这些默认值约束包含的内容？

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

） 去

以上是在 SSMS 中生成脚本时创建的定义。如您所见，默认值不在脚本中。

sql-server user-defined-table-type

2 个回答

  1. Best Answer

    查找用户定义的表类型：

    -- UDTT will have a 'TT prefix'
select * from sys.objects where type = 'TT' and name like '%LogElements%';

    在我的输出中，该对象被调用TT_LogElements_3A4CA8FD并具有object_id.978102525

    现在我们可以使用类似于 Ryan Cooper 的答案的脚本，将其object_id插入WHERE子句中，如下所示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;

    这是我的示例输出，将其转换为 T-SQL 脚本以重新创建它们相当简单。

    [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')
    • 3

  2. 您可以使用以下查询查看默认值及其定义：

    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'

    编辑以查找用户定义的类型：

        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'
    • 2

相关问题