how to create default constraint for each table

Question

I have around 150 tables in my database and now I need to add a column constraint(InsertedOn) on to each and every table with a default value of GetDate()

I have tried below code to accomplish the task

exec sp_msforeachtable 'ALTER TABLE ? ADD CONSTRAINT DF_InsertedOn DEFAULT GetDate() FOR [InsertedOn]';

But my problem is constraint name,The above code fails when its trying to create the constraint for the second table since the name of the constraint in use

Is there any way to accomplish the task using the same sp_msforeachtable ?

Thank you.

Answer 1

To generate scrips you can use this code. It uses system views sys.tables, sys.schemas and sys.columns

DECLARE @ColumnName VARCHAR(100) = 'InsertedOn'

SELECT
    'ALTER TABLE ' + s.name + '.' + t.name + 
    ' ADD CONSTRAINT DF_' + t.name + '_' + @ColumnName + 
    ' DEFAULT GetDate() FOR ' + @ColumnName
FROM sys.tables t
JOIN sys.schemas s ON s.schema_id = t.schema_id
WHERE EXISTS (
        SELECT *
        FROM sys.columns c
        WHERE c.object_id = t.object_id
            AND name = @ColumnName
    )

Answer 2

Try this one -

DECLARE @SQL NVARCHAR(MAX)
SELECT @SQL = STUFF((
    SELECT CHAR(13) + 'ALTER TABLE [' + s.name + '].[' + o.name  + '] 
        ADD [InsertedOn] DATETIME NOT NULL CONSTRAINT [DF_' + o.name + '_InsertedOn] DEFAULT GETDATE()'
    FROM sys.objects o WITH (NOWAIT)
    JOIN sys.schemas s WITH (NOWAIT) ON o.[schema_id] = s.[schema_id]
    WHERE NOT EXISTS(
            SELECT 1
            FROM sys.columns c WITH (NOWAIT)
            WHERE c.[object_id] = o.[object_id]
                AND c.name = 'InsertedOn'
        )
        AND o.[type] = 'U'
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

PRINT @SQL
EXEC sys.sp_executesql @SQL

Output -

ALTER TABLE [tt].[t1] 
ADD [InsertedOn] DATETIME NOT NULL CONSTRAINT [DF_t1_InsertedOn] DEFAULT GETDATE()
ALTER TABLE [tt].[t2] 
ADD [InsertedOn] DATETIME NOT NULL CONSTRAINT [DF_t2_InsertedOn] DEFAULT GETDATE()