Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

dtproperties Expand / Collapse
Author
Message
Posted Tuesday, September 4, 2001 12:13 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Tuesday, July 31, 2007 8:20 AM
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"
Post #941
Posted Tuesday, September 4, 2001 5:21 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: Moderators
Last Login: Yesterday @ 2:30 PM
Points: 6,784, Visits: 1,899
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
Post #21241
Posted Tuesday, September 4, 2001 6:16 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Wednesday, June 4, 2014 12:29 PM
Points: 1,931, Visits: 234
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
Post #21242
Posted Thursday, September 13, 2001 9:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 26, 2002 12:00 AM
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






Post #21243
Posted Thursday, September 13, 2001 9:24 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 5:59 PM
Points: 31,082, Visits: 15,529
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
Post #21244
Posted Thursday, September 13, 2001 11:59 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, April 26, 2002 12:00 AM
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





Post #21245
Posted Friday, January 28, 2005 7:08 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, September 23, 2014 12:46 PM
Points: 596, Visits: 1,691

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




Post #158240
Posted Thursday, October 12, 2006 3:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 22, 2013 4:15 AM
Points: 20, 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 ?

Post #314881
Posted Thursday, October 12, 2006 6:54 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 9, 2014 6:02 AM
Points: 2,674, Visits: 697

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/
Post #314938
Posted Thursday, October 12, 2006 5:50 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 5:14 PM
Points: 35,266, Visits: 31,756

--===== 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #315136
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse