September 17, 2012 at 12:48 am
In SQL2012, If i create a temporary table with a column default, it isn't reflected in sys.default_constraints table anymore:
IF OBJECT_ID('tempdb..#t_test') is not null
DROP TABLE #t_test
GO
CREATE TABLE #t_test (i INT NOT NULL DEFAULT 1, z INT)
SELECTCOUNT(*)
FROMtempdb.sys.default_constraints
WHEREparent_object_id= OBJECT_ID('tempdb..#t_test')
SELECT@@VERSION
-----------
0
(1 row(s) affected)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
In SQL2008 this works fine:
-----------
1
(1 row(s) affected)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64)
Sep 16 2010 19:43:16
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2)
Edit:
With regular (non #t-) tables this works fine
September 20, 2012 at 2:41 am
Following workaround might be of interest for others (this works in both 2008 & 2012):
CREATE TABLE #t_test (i INT DEFAULT 1)
SELECTc.COLUMN_NAME, c.COLUMN_DEFAULT
FROMtempdb..syscolumns sc (NOLOCK)
INNER JOIN tempdb..sysobjects so (NOLOCK)
ONso.id = sc.id
INNER JOIN tempdb.INFORMATION_SCHEMA.COLUMNS c
ONc.TABLE_NAME = so.name
WHEREso.id = OBJECT_ID('tempdb..#t_test')
September 23, 2012 at 9:36 am
siggemannen (9/17/2012)
In SQL2012, If i create a temporary table with a column default, it isn't reflected in sys.default_constraints table anymore
Yes it is a bug in the definition of sys.default_constraints:
CREATE VIEW sys.default_constraints AS
SELECT name, object_id, principal_id, schema_id, parent_object_id,
type, type_desc, create_date, modify_date,
is_ms_shipped, is_published, is_schema_published,
property AS parent_column_id,
object_definition(object_id) AS definition,
is_system_named
FROM sys.objects$
WHERE type = 'D ' AND parent_object_id > 0
The bug is in the AND part of the WHERE clause. Object IDs for temporary tables are negative in SQL Server 2012. Report the bug at connect.microsoft.com
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 23, 2012 at 9:43 am
If you can live without the parent_column_id and is_system_named columns, the following will work:
SELECT name, object_id, principal_id, schema_id, parent_object_id,
type, type_desc, create_date, modify_date,
is_ms_shipped, is_published, is_schema_published,
object_definition(object_id) AS definition
FROM tempdb.sys.objects
WHERE type = 'D ' AND parent_object_id <> 0
The missing two columns and the hidden sys.objects$ table are only accessible from the DAC.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
October 2, 2012 at 1:02 pm
Thank you, good find of object_id thingy...
I have added my first connect item now: https://connect.microsoft.com/SQLServer/feedback/details/765777/sys-default-constraints-empty-for-temporary-tables-in-tempdb
BR,
Sergey
October 2, 2012 at 2:16 pm
siggemannen (10/2/2012)
Added my vote.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply