Wednesday, July 3, 2013

Generate default constraint scripts of selected database

SET NOCOUNT ON

SELECT 'ALTER TABLE ' + B.NAME + char(10) 
+'ADD CONSTRAINT ' + C.NAME + ' DEFAULT ' + C.DEFINITION + ' FOR ' + A.NAME + CHAR(10) 
FROM SYS.ALL_COLUMNS A 
INNER JOIN
SYS.TABLES B 
ON 
A.OBJECT_ID = B.OBJECT_ID
INNER JOIN
SYS.DEFAULT_CONSTRAINTS C
ON A.DEFAULT_OBJECT_ID = C.OBJECT_ID


/*====================================

alter table TABLE_NAME
ADD CONSTRAINT CONSTRAINT_NAME
default (DEFAULT_VALUE) for COLUMN_NAME


================================= */

SET NOCOUNT OFF



No comments:

Post a Comment