CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008-r2
bstorrie
bstorrie

Reputation: 161

SQL Server - Add default constraints to all tables

Is there a way to write a script that examines each table in the database, looking for a uniqueidentifier column named ID, and then add a DEFAULT constraint of NEWID() to the column if a DEFAULT constraint does not exist?

Upvotes: 4

Views: 2064

Answers (1)

Phil Hunt
Phil Hunt

Reputation: 8521

Edited per OP's commented clarification

This script creates a DEFAULT constraint of NEWID() on any uniqueidentifier columns named ID that do not already have a DEFAULT constraint.

SET NOCOUNT ON

DECLARE @tableVarId int
DECLARE @alterSql   nvarchar(MAX)

DECLARE @columnsNeedingDefault TABLE
(
  TableVarId    int IDENTITY (1,1) PRIMARY KEY,
  AlterSql      nvarchar(MAX)
)

INSERT INTO @columnsNeedingDefault (AlterSql)
SELECT
  'ALTER TABLE ' + QUOTENAME(a_Table.[name]) +
  ' ADD CONSTRAINT [DF_' + a_Table.[name] + '_' + a_Column.name +
  '] DEFAULT NEWID() FOR ' + QUOTENAME(a_Column.name)
FROM
  sys.columns AS a_Column
INNER JOIN
  sys.tables AS a_Table ON a_Column.[object_id] = a_Table.[object_id]
WHERE
  a_Column.[name] = 'ID'
  AND
  a_Column.user_type_id = 36 -- uniqueidentifier
  AND
  NOT EXISTS
  (
    SELECT
      *
    FROM
      sys.default_constraints AS a_DefaultConstraint
    WHERE   a_DefaultConstraint.parent_object_id = a_Table.[object_id]
      AND   a_DefaultConstraint.parent_column_id = a_Column.column_id
      AND   a_DefaultConstraint.type_desc = 'DEFAULT_CONSTRAINT'
  )

SET @tableVarId = (SELECT MIN(TableVarId) FROM @columnsNeedingDefault)
WHILE @tableVarId IS NOT NULL
  BEGIN
    SELECT @alterSql = AlterSql FROM @columnsNeedingDefault

    PRINT @alterSql
    EXEC sp_executesql @alterSql

    SET @tableVarId = (SELECT MIN(TableVarId) FROM @columnsNeedingDefault
                       WHERE TableVarId > @tableVarId)
  END

Upvotes: 3

Related Questions