Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


dtproperties


dtproperties

Author
Message
ckempste
ckempste
SSC Eights!
SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)SSC Eights! (885 reputation)

Group: General Forum Members
Points: 885 Visits: 1
Hi all

What is the "dtproperties" system table? when quering sysobjects this table has an xtype of 'U' which really screw's up scripts if you rely on this col only to skip system tables.
Any ideas? it seems to have some of my custom role in its contents.

Cheers

Chris


Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Andy Warren
Andy Warren
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: Moderators
Points: 7233 Visits: 2679
I was thinking that this was used to store diagrams - maybe Im confused? And I think remember hearing that it was a user table because of some kind of internal squabble at MS.

Andy

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter
Brian Knight
Brian Knight
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: Moderators
Points: 1955 Visits: 235
Andy is correct. It stores diagrams. MS recently turned the XTYPE to "U" in a late SQL 7 service pack so you could transfer them from server to server. It's always showing up in ER/WIN diagrams though as a nasty side effect.

Brian Knight
bknight@sqlservercentral.com
http://www.sqlservercentral.com/columnists/bknight

Brian Knight
Free SQL Server Training Webinars
Ripple
Ripple
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 1
Here is a script to purge the dt* stuff out of your database (if you desire) as well as the actions that create the dt* objects.

/**********************************************************************************
*
* VSS: START -----------------------------------------------------------------
* VSS: $Workfile: SUPPORT_DROP_DT_OBJECTS.SQL $
* VSS: $Archive: /Projects/FCP2.0/src/SQL/Common Objects/Utilities/SUPPORT_DROP_DT_OBJECTS.SQL $
* VSS: $Author: A217780 $
* VSS: $Date: 4/27/01 9:41a $
* VSS: $Modtime: 4/27/01 9:13a $
* VSS: $Revision: 2 $
* VSS: $Header: /Projects/FCP2.0/src/SQL/Common Objects/Utilities/SUPPORT_DROP_DT_OBJECTS.SQL 2 4/27/01 9:41a A217780 $
* VSS: STOP ------------------------------------------------------------------
*
* DESCRIPTION: This script is used to purge all the dt* stored procedures and tables
* from the current database. The dt* objects get into the database as a result
* of using the 'Databas Diagram' feature of SQL Server Enterpise Manager.
*
* Here are the actions in SQL Server Enterprise Manager which create the dt* objects:
* --> Expand Databases
* --> Expand a target database
* --> Click on Diagrams
* --> Right mouse and click 'New Diagram' (As soon as you do this, the table and
* procedures are added without prompting)
*
*
* MODIFICATION LOG:
*
* Rev. Date Developer Brief Description of Change
* ----- ---------- ------------------- -------------------------------------
* 1 04/24/2001 Brendan H. Sullivan * Initial Version
* 2 04/27/2001 Brendan H. Sullivan * Added a check for existence.
**********************************************************************************/

IF OBJECT_ID('dt_addtosourcecontrol') IS NOT NULL
BEGIN
DROP PROCEDURE dt_addtosourcecontrol
IF OBJECT_ID('dt_addtosourcecontrol') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dt_addtosourcecontrol >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dt_addtosourcecontrol >>>'
END
go
IF OBJECT_ID('dt_adduserobject') IS NOT NULL
BEGIN
DROP PROCEDURE dt_adduserobject
IF OBJECT_ID('dt_adduserobject') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dt_adduserobject >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dt_adduserobject >>>'
END
go
IF OBJECT_ID('dt_adduserobject_vcs') IS NOT NULL
BEGIN
DROP PROCEDURE dt_adduserobject_vcs
IF OBJECT_ID('dt_adduserobject_vcs') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dt_adduserobject_vcs >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dt_adduserobject_vcs >>>'
END
go
IF OBJECT_ID('dt_checkinobject') IS NOT NULL
BEGIN
DROP PROCEDURE dt_checkinobject
IF OBJECT_ID('dt_checkinobject') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dt_checkinobject >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dt_checkinobject >>>'
END
go
IF OBJECT_ID('dt_checkoutobject') IS NOT NULL
BEGIN
DROP PROCEDURE dt_checkoutobject
IF OBJECT_ID('dt_checkoutobject') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dt_checkoutobject >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dt_checkoutobject >>>'
END
go
IF OBJECT_ID('dt_displayoaerror') IS NOT NULL
BEGIN
DROP PROCEDURE dt_displayoaerror
IF OBJECT_ID('dt_displayoaerror') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dt_displayoaerror >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dt_displayoaerror >>>'
END
go
IF OBJECT_ID('dt_droppropertiesbyid') IS NOT NULL
BEGIN
DROP PROCEDURE dt_droppropertiesbyid
IF OBJECT_ID('dt_droppropertiesbyid') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dt_droppropertiesbyid >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dt_droppropertiesbyid >>>'
END
go
IF OBJECT_ID('dt_dropuserobjectbyid') IS NOT NULL
BEGIN
DROP PROCEDURE dt_dropuserobjectbyid
IF OBJECT_ID('dt_dropuserobjectbyid') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dt_dropuserobjectbyid >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dt_dropuserobjectbyid >>>'
END
go
IF OBJECT_ID('dt_getobjwithprop') IS NOT NULL
BEGIN
DROP PROCEDURE dt_getobjwithprop
IF OBJECT_ID('dt_getobjwithprop') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dt_getobjwithprop >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dt_getobjwithprop >>>'
END
go
IF OBJECT_ID('dt_getpropertiesbyid') IS NOT NULL
BEGIN
DROP PROCEDURE dt_getpropertiesbyid
IF OBJECT_ID('dt_getpropertiesbyid') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dt_getpropertiesbyid >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dt_getpropertiesbyid >>>'
END
go
IF OBJECT_ID('dt_getpropertiesbyid_vcs') IS NOT NULL
BEGIN
DROP PROCEDURE dt_getpropertiesbyid_vcs
IF OBJECT_ID('dt_getpropertiesbyid_vcs') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dt_getpropertiesbyid_vcs >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dt_getpropertiesbyid_vcs >>>'
END
go
IF OBJECT_ID('dt_isundersourcecontrol') IS NOT NULL
BEGIN
DROP PROCEDURE dt_isundersourcecontrol
IF OBJECT_ID('dt_isundersourcecontrol') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dt_isundersourcecontrol >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dt_isundersourcecontrol >>>'
END
go
IF OBJECT_ID('dt_removefromsourcecontrol') IS NOT NULL
BEGIN
DROP PROCEDURE dt_removefromsourcecontrol
IF OBJECT_ID('dt_removefromsourcecontrol') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dt_removefromsourcecontrol >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dt_removefromsourcecontrol >>>'
END
go
IF OBJECT_ID('dt_setpropertybyid') IS NOT NULL
BEGIN
DROP PROCEDURE dt_setpropertybyid
IF OBJECT_ID('dt_setpropertybyid') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dt_setpropertybyid >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dt_setpropertybyid >>>'
END
go
IF OBJECT_ID('dt_validateloginparams') IS NOT NULL
BEGIN
DROP PROCEDURE dt_validateloginparams
IF OBJECT_ID('dt_validateloginparams') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dt_validateloginparams >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dt_validateloginparams >>>'
END
go
IF OBJECT_ID('dt_vcsenabled') IS NOT NULL
BEGIN
DROP PROCEDURE dt_vcsenabled
IF OBJECT_ID('dt_vcsenabled') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dt_vcsenabled >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dt_vcsenabled >>>'
END
go
IF OBJECT_ID('dt_verstamp006') IS NOT NULL
BEGIN
DROP PROCEDURE dt_verstamp006
IF OBJECT_ID('dt_verstamp006') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dt_verstamp006 >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dt_verstamp006 >>>'
END
go
IF OBJECT_ID('dt_whocheckedout') IS NOT NULL
BEGIN
DROP PROCEDURE dt_whocheckedout
IF OBJECT_ID('dt_whocheckedout') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dt_whocheckedout >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dt_whocheckedout >>>'
END
go

IF OBJECT_ID('dtproperties') IS NOT NULL
BEGIN
DROP TABLE dtproperties
IF OBJECT_ID('dtproperties') IS NOT NULL
PRINT '<<< FAILED DROPPING TABLE dtproperties >>>'
ELSE
PRINT '<<< DROPPED TABLE dtproperties >>>'
END
go



Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36184 Visits: 18751
I'd be wary to removing any "system" like tables. HAve you noticed any issues? Do they get recreated with a new diagram?

Steve Jones
steve@dkranch.net

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Ripple
Ripple
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 1
I'm always wary when removing objects that the server put there as well. The dt* objects appear to be used only for 'diagrams'. We are not using the diagraming feature in SQL Server, so we do not have a need for them. Yes, they get put back when you take the following action:

* Here are the actions in SQL Server Enterprise Manager which create the dt* objects:
* --> Expand Databases
* --> Expand a target database
* --> Click on Diagrams
* --> Right mouse and click 'New Diagram' (As soon as you do this, the table and
* procedures are added without prompting)


I have found no repercutions from removing these objects.

I think SQL Server is doing a dis-service by jamming the dt* objects into a user database without prompting the user they are about to create a pile of objects.

- Brendan



Indianrock
Indianrock
SSC Eights!
SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)SSC Eights! (864 reputation)

Group: General Forum Members
Points: 864 Visits: 2258

My problem is that the dt objects appear in some databases as system objects and as user objects in others. Using Redgate's sql compare, I wind up with a report showing differences in these objects when they are present in both databases.

Rather than remove them, how might we get them set to system objects in a DB where they appear as user objects.

Randy





Atik
Atik
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 26

When i create database by using installation program made by

Redgate's schema generation in .Net

on my local server Dtproperties table is created. But some other servers we have i see that Dtproperties table is not created. Why it happens ? is t because of some service pack differences? or what is the reason ?


colin.Leversuch-Roberts
colin.Leversuch-Roberts
SSCrazy
SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)SSCrazy (2.7K reputation)

Group: General Forum Members
Points: 2725 Visits: 715

Think you'll also find using visual studio against a database will create all those tables - I have successfully removed them over the years without any problems - some software used with releases and code generation can end up doing all sorts of unncessary operations on these objects. ( mind it does the same thing when databases are replicated too!! )

( I don't actually always remove them - just when they give issues - like the compare ones for instance. )



The GrumpyOldDBA
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Jeff Moden
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45173 Visits: 39925

--===== Return user created tables only. Ignores dtProperties, as well
SELECT Name
FROM dbo.SysObjects
WHERE OBJECTPROPERTY(OBJECT_ID(Name),'IsMSShipped')=0
AND XType = 'U'

'nuff said...



--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search