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 ('')?

Look into cursors and dynamic SQL. Alternatively, just generate all your statements using regex replace in a text editor. — Lunyx, Dec 30 '16 at 15:18
It makes me wonder why you are going through such great lengths to avoid `NULL` values. — Gordon Linoff, Dec 30 '16 at 15:19
@GordonLinoff Not my doing. I picked up an old access database and converted it over to MS SQL. They did not do a great job at building the database in access. — StealthRT, Dec 30 '16 at 15:21
This can give you an idea `SELECT 'ALTER TABLE [dbo].[Table_3] ADD CONSTRAINT [DF_Table_3_'+name+'] DEFAULT ('') FOR ['+name+']' FROM sys.columns WHERE [object_id] = OBJECT_ID('[dbo].[Table_3]');` — Hackerman, Dec 30 '16 at 15:28
@StealthRT -- That was not Gordon's point. In SQL nulls are a good thing. If the data model has nulls instead of empty strings this can be super useful. Replacing all the nulls because you are not used to working working with nulls is making us worried -- we know in a short period you will be asking questions that would have been solved by leaving the nulls in. — Hogan, Dec 30 '16 at 15:31

score 7 · Accepted Answer · edited Dec 30 '16 at 15:34

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

100+ tables was just an example of more than just a handful that would be a lot of manually copy and pasting... — StealthRT, Dec 30 '16 at 15:23

score 0 · Answer 2 · edited Dec 30 '16 at 16:00

0

This script should work

 select 'alter table TABLE add constraint DF_TABLE_' 
         + name  + ' DEFAULT ('''') FOR ' + name  
 from sys.columns 
 where object_id = object_id('TABLE')

edited Dec 30 '16 at 16:00

StealthRT

10,108
40
183
342

answered Dec 30 '16 at 15:46

giacomo23

21
4

This answer would be improved if you would explain where `name` comes from. The OP said they don't want to have to manually specify each column. Your answer, as it stands, doesn't explain how to get around that problem. – devlin carnate Dec 30 '16 at 15:57

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

2 Answers2