dtproperties

  • ckempste

    SSCoach

    Points: 17983

    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

    SSC Guru

    Points: 119694

    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

  • Brian Knight

    One Orange Chip

    Points: 27249

    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

  • Ripple

    Right there with Babe

    Points: 777

    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 - SSC Editor

    SSC Guru

    Points: 720887

    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

  • Ripple

    Right there with Babe

    Points: 777

    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

    SSC-Insane

    Points: 20333

    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

    SSC Veteran

    Points: 268

    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

    SSC Guru

    Points: 52551

    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/

  • Jeff Moden

    SSC Guru

    Points: 997103

    --===== 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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Sreejith Sreedharan

    SSCertifiable

    Points: 5403

    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

  • Jeff Moden

    SSC Guru

    Points: 997103

    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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • radhakrishna.reddy

    Valued Member

    Points: 73

    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 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply