What the hell was that constraint called?

Over the years I have been guilty so many times of writing a rollback script that rolls column additions to a table. Which is essentially a script that will drop the added columns. When i create a new column with a default value i ALWAYS use a constraint name which i created. In the event you inherit someone else database who does not (not mine of course) :) forgetting to drop the default value constraint (which is something only an inexperienced DBA would do NOT ME of course) :)

As luck would have it if you forget to add your own name SQL Server will add one for you. with a very easy to remember name. If DF__Beta_foo__bar__5629CD9C is easy to remember that is. So all of that being said, here is a quick script to get a list of all the constraints and the tables / columns they are on so you can easily add them to your rollback script.

 SELECT  
   b.name AS TABLE_NAME, d.name AS COLUMN_NAME, a.name AS CONSTRAINT_NAME,  
   c.text AS DEFAULT_VALUE  
 FROM  
   sys.sysobjects a  
 INNER JOIN (  
        SELECT name, id FROM sys.sysobjects WHERE xtype = 'U'  
       ) b  
 ON ( a.parent_obj = b.id )  
 INNER JOIN sys.syscomments c  
 ON ( a.id = c.id )  
 INNER JOIN sys.syscolumns d  
 ON ( d.cdefault = a.id )  
 WHERE  
   a.xtype = 'D'  
 ORDER BY  
   b.name, a.name