dtproperties
-
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" -
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 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
http://www.sqlservercentral.com/columnists/bknight
Brian Knight
Free SQL Server Training Webinars -
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
-
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
-
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
-
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
-
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 ?
-
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. )
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
--===== 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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
I have used Status and exclude system objects(Tables and Procedures)
SELECT Name ,*
FROM dbo.SysObjects
WHERE XType = 'U'
and status > 0
order by 1
Am I wrong with that assumption?
Thanks
Sreejith
-
Nope... that works too... I just thought I'd show another way.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
As per my knowledge, dt_* objects are using for database diagram, but if I dont use this option also dt_* objects are creating, even if its existing also, its dropping the existing one and recreating.....
Can some one give me more inforamation about dt_* objects creation....?
Thanks in advance
Reddy 🙂
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply