SQL Server Performance Forum – Threads Archive

Where is dt_properties

When I use sp_tables in a user db I can see a table dt_properties.But I am unable to see this table using EM.What is this tbl all about.Pls guide

---

dtproperties is a system table.
ur EM must be set to hide system table, so u cant see it using EM

---

I can see all other system tables except this one .How can I see this ?

---

For a SQL 7.0 server, the dtproperties table is available in EM only when a database diagram is available for the database. Look at the "pubs" database. – By the way, this is using the SQL 2000 EM and QA. In SQL 2000 the table is always visible in EM. Just a silly thing in EM, because even for a SQL 7.0 server you can actually see the table in the SQL 2000 QA object browser.

---

All database diagrams are stored in a sytem table called dt_properties. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

In some database I can see this table and in others I cant .When I create a new db I cant see this table.Instead I got this error
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name ‘dtproperties’.
When it comes and from How it comes.Can u pls explain me.

---

This table will be used and created only when any of database diagrams process is used, and in the case of above error try to create a sample diagram and see the table contents. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

hi aaronsandy, as said by adriaan,
dtptoperties table stores database diagrams. so as soon as u create new database diagram dtproperties table will be populated.
when u create a new database dtproperties table gets created.
only few system database like Model,tempdb,master,msdb dont create this table as they dont allow to create diagram.
so create a new db and then create diagram to c content of dtproperties.

---

Hello, sorry, but my english is not good.
dtproperties only stores database diagrams? Or stores any other information? Thank you. Guille

---

Hi Guille,
dtproperties table stores all the property related to database diagram with objectid,property,value foreach diagram with auto increment field.

---

Thank you. I need to know if dtproperties stores database diagrams only, because I need copy the database diagrams from a database to another database and only database diagrams.
If dtproperties stores other information besides the database diagrams, I will make mistakes. Thank you, again Guille

---

Problem is that all those id values change between databases, so you can’t do a simple insert from one db into the next one.

---

Thank you. To avoid that problem I am using this sentence
SET IDENTITY_INSERT dbo.dtproperties ON
<Copy records>
SET IDENTITY_INSERT dbo.dtproperties OFF What do you think about that? Thank you, again. Guille

---

The IDENTITY_INSERT switch allows you to enter values into a column that is defined as IDENTITY, which in dtproperties is the "id" column. The "objectid" column is the problem here. You’re inserting the objectid of objects in one database into a system table of another database. Even if the objects in themselves are identical, they probably have a objectid that is different from the one in the other database. Well actually, you’re inserting rows directly into a system table, which is taboo in itself. And finally, I’m not sure you can insert an IMAGE column just like that. Anyway – for fun, try running this query – just replace TargetDB and SourceDB with the names of your two database: USE TargetDB
GO SELECT OBJECT_ID(OBJECT_NAME(T.objectid)) AS TargetIdForTargetNameForSourceId, OBJECT_NAME(T.objectid) AS TargetNameForSourceId, O.name AS SourceNameForSourceId
FROM SourceDB.dbo.dtproperties AS T
INNER JOIN SourceDB.dbo.sysobjects AS O ON T.id = O.id
WHERE (T.objectid <> OBJECT_ID(OBJECT_NAME(T.objectid)))
OR (OBJECT_ID(OBJECT_NAME(T.objectid)) IS NULL)
OR (OBJECT_NAME(T.objectid) <> o.name)

---

I have executed these sentences and the result was: TargetIdForTargetNameForSourceId TargetNameForSourceId SourceNameForSourceId
22 sysmembers sysprotects
22 sysmembers sysfulltextnotify
NULL NULL sysfiles
NULL NULL sysfilegroups I am not understanding the result. My source database and my target database are the "same" database. The diferences between them are modifications in source database. I need synchronize both databases and I need synchronize the database diagrams, that is the reason because I want to copy te dtproperties’ records. In this case, I wil make mistakes? Thank you.

---

Sorry, I shouldn’t have posted such a confusing query — but then again it really should confuse you because you shouldn’t be touching this feature with anything but Enterprise Manager, or perhaps a 3rd party tool. The same as with the regular system tables, don’t run any kind of action query against dtproperties!

---

]]>