sys.default_constraints empty for temporary tables in tempdb - by siggemannen

Status : 

  Fixed<br /><br />
		This item has been fixed in the current or upcoming version of this product.<br /><br />
		A more detailed explanation for the resolution of this particular item may have been provided in the comments section.


8
0
Sign in
to vote
ID 765777 Comments
Status Closed Workarounds
Type Bug Repros 3
Opened 10/2/2012 11:56:30 AM
Access Restriction Public

Description

In SQL Server 2012, If i create a temporary table with a column default, it isn't reflected in sys.default_constraints table anymore.

This is because sys.default_constraints View works with object_id > 0 but all temporary tables have object_id < 0 in SQL Server 2012.

Perhaps a fix of view definition could be:

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
Sign in to post a comment.
Posted by Microsoft on 2/26/2013 at 7:15 PM
Hi, Thank you for your feedback. We have investigated the issue and the fix will be available in the next release of SQL Server. Thanks & Regards, Pooja Harjani - Program Manager - SQL Server