Constraints, Columns, and Tables oh My...
Posted by John Trumbul on Wednesday, October 10, 2012
Under: SQL Scripts
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.
[code]
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
[/code]
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.
[code]
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
[/code]
In : SQL Scripts