SQL Server – Database Diagram – Metadata

Background

Wanted to quickly document the database objects that are provisioned when the user requests “Database Diagramming” on a database.

Please keep in mind that this is not an instance level operation.

It targets the context database.

 

List

Object Type Object Usage Code
Function
Function dbo.fn_diagramobjects Counts number of system supplied database diagram helper objects currently available SELECT [kb].dbo.fn_diagramobjects()
Stored Procedure
dbo.sp_alterdiagram Alter existing diagram update dbo.sysdiagrams ( data passed in via SP )
dbo.sp_creatediagram Create new diagram inserts into dbo.sysdiagrams ( data passed in via SP )
dbo.sp_dropdiagram Drop existing diagram  delete from dbo.sysdiagrams ( data passed in via SP )
dbo.sp_helpdiagramdefinition Return diagram’s definition select version, definition FROM dbo.sysdiagrams ( diagram_id passed in via SP )
dbo.sp_helpdiagrams Return diagram information SELECT *
FROM sysdiagrams
WHERE ( @dboLogin = 1 OR USER_NAME(principal_id) = @user)
AND ( @diagramname IS NULL OR name = @diagramname)
AND ( @owner_id IS NULL OR principal_id = @owner_id)
dbo.sp_renamediagram Rename diagram
dbo.sp_upgraddiagrams Copies diagram data from dbo.[dtproperties] into dbo.sysdiagrams
Table
dbo.sysdiagrams Contains metadata on user created diagrams

Tables

dbo.sysdiagrams

Indepth

SQL Server Profiler

Scenario

New database Diagram Requested

When a new Database Diagram is requested…

 

Database Diagram Preparation

When a database is being prepared for Database Diagram.

Summary

It appears that diagram metadata were previously stored in  dbo.dtproperties.

But, they are now stored in dbo.sysdiagrams.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s