MS SQL define default value for each column name via query loop

Question

Hey all I am hoping someone has a script that helps with bulk

Say I have a table like so:

CREATE TABLE [dbo].[Table_3](
    [value1] [varchar](50) NOT NULL,
    [value2] [varchar](50) NOT NULL,
    [value3] [varchar](50) NOT NULL,
    [value4] [nchar](10) NOT NULL
) ON [PRIMARY]

And instead of NOT NULL I would like to enter a blank for the value (a.k.a. '' ).

Now I know it can be done this way:

ALTER TABLE [dbo].[Table_3] ADD  CONSTRAINT [DF_Table_3_value1]  DEFAULT ('') FOR [value1]
GO
ALTER TABLE [dbo].[Table_3] ADD  CONSTRAINT [DF_Table_3_value2]  DEFAULT ('') FOR [value2]
GO
etc...

But I do not want to have to manually do that for each column since some of my tables have 100+ columns.

Is there any type of query that will loop through all of my columns and set them to my default ('')?

Answer 1

Here is one way

DECLARE @tblName VARCHAR(MAX)
DECLARE @SQL VARCHAR(MAX)

SET @tblName = 'Table_3'
SET @SQL = (SELECT 'ALTER TABLE [dbo].[' + @tblName + '] ADD  CONSTRAINT [DF_' + @tblName + '_'
                   + COLUMN_NAME + '] DEFAULT ('''') FOR '
                   + Quotename(COLUMN_NAME) + '; '
            FROM   INFORMATION_SCHEMA.COLUMNS
            WHERE  TABLE_NAME = @tblName 
                   AND COLUMN_NAME LIKE 'value%'
                   AND IS_NULLABLE = 'NO'
            FOR XML PATH(''))

--PRINT @SQL
EXEC (@SQL) 

Having 100+ columns in a table doesn't sound right. You may have to restructure your table