"dt_*" Stored Procedures

I was recently trying to find all objects in our production databases that had GRANTs to public. I found a number of "dt_*" stored procedures. In researching them, I found some outdated information.

This post states that "They are safe to delete, provided you are not using full-blooded source control and Visual Studio integration, and you do not have any diagrams that you want to keep around."

I won't comment about the VSS issues, but the diagram issues appear to be outdated. (That post is from March of 2005.)

There is similar information in this post. Jens Suessmeyer, a moderator, comments, "Yes, if you need created database diagrams you should leave the dbpoperties.[sic]" My assumption is that he meant the dtpoperties table. In another post, Steve Twitchell states,

You can delete the dt_* procedures if you're sure no one is using the database designer, table designer, view designer, or query designer in Visual Studio, SQL Server Management Studio, or SQL Enterprise Manager. The database tools functionality will recreate them (after prompting for permission) if it thinks it needs them. If you delete the dt_* tables, you'll delete all the database diagrams stored on the server.

Time to Test

In SQL Server 2005 (Developer Edition), I first created a new database and checked for stored procedures. No diagram-related SPs existed. I right-clicked on the "Database Diagrams" folder and got a message about the database not having support objects. I clicked Yes to create them.

I then created a diagram and re-checked for new objects. I found a dbo.sysdiagrams system table and seven dbo.sp_*diagram* system stored procedures.

These objects appear to be the SQL Server 2005 versions of the old dt_ objects.

Conclusion

This does not answer all possible questions regarding dt_ stored procedures or their SQL Server 2005 equivalents, but hopefully it is useful.

As always, test in your own environment. For us, we're going to remove all the dt_* procedures.