Change all null string columns to an empty string, correct bad initial design

SQL query explained in C:

foreach row in Table

        foreach column in row

                     IF (column is type string AND null)
                         {column = '';}

There are several types of string columns (nvarchar, varchar, char, text along with non string types like int)  
These are live tables with data.  I can stop it tonight for several hours to do this.

After I run this query I will with SQL Server Management Studio manually set all string columns to (Allow nulls = NO) and (default = '') .  empty string

Unless there is an easier way to set all string columns to (Allow nulls = NO) and (default = '') that is safe?  Maybe a script?  I am new to SQL.

Kind Regards
Sam
SamCashAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

chaauCommented:
Sometimes null in table is not a "bad design". Are you sure that the null is not used anywhere in the application as a "value not defined"?
SamCashAuthor Commented:
Thank you.

Interesting point, luckily no, it is not used anywhere in the design.

And yes I will backup before I execute...

Thanks again
Sam
plusone3055Commented:
with lots of colums in the table

update yourtablename
set
    column1=''if(column1, null),
    column2=''if(column2, null)

Open in new window

The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

SamCashAuthor Commented:
Thanks,

What if the column is not a string column, like an int column?  I only want to change there string type columns.

Is there a way to iterate through all columns in a table.  There are many tables with lots of columns.  I will change the table name each time, but won't have time to retype all the different columns?

For example:

foreach row in the TABLE

   check the column data type,  If the data type is one of the string types then:

set this column = '' where this column = null
else check the next column

I hope this clarifies my question.

Regards
Sam
Goodangel MatopeSoftware ArchitectCommented:
I have written something that works. The script below has two cursors. One gets all the string type columns in your specified table and builds a generic update query which will update all null the null string columns to empty strings. The second part then uses that generic update query to update your actual table.

I have used for clarity a table called tblClient, and you should replace this with your own table name. The only problem (Which I hope you can live with) is that you have to specify the table name in two places. once as a variable in the first cursor and once literally in the second cursor. Also, depending on your field names, you will need to change the WHERE clause in the generic query and also in the second cursor.

I have tested it and it works

DECLARE @ColumnName VARCHAR(100);
DECLARE @Query VARCHAR(MAX);
DECLARE @UpdateQuery VARCHAR(MAX);
DECLARE @TableName VARCHAR(100);

DECLARE @ID INT;

SET @TableName = 'tblClient'

DECLARE StringColumns CURSOR FOR
SELECT [COLUMN_NAME]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'' + @TableName + ''
AND DATA_TYPE IN ('varchar','char','nvarchar','text','ntext')

OPEN StringColumns;
FETCH NEXT FROM StringColumns INTO @ColumnName;

SET @Query = '
UPDATE ' + @TableName + '
SET '

WHILE @@FETCH_STATUS = 0
BEGIN

		SET @Query = @Query + '
		' + @ColumnName + ' = ISNULL(' + @ColumnName + ',''''),';

		FETCH NEXT FROM StringColumns INTO @ColumnName;

END

CLOSE StringColumns;
DEALLOCATE StringColumns;

SET @Query = @Query + 'WHERE [ID] = @ID'

SET @Query = REPLACE(@Query,',WHERE','
 WHERE')
 
PRINT @Query
-- @Query is the query that will update each row so we now create a cursor to update the table rows
------------------------------------------------------------------------------------------

DECLARE TableRows CURSOR FOR  /* We get all rows from the table and run the query we creatd on them */
SELECT [ID] FROM tblClient

OPEN TableRows
FETCH NEXT FROM TableRows INTO @ID;

WHILE @@FETCH_STATUS = 0
BEGIN
		SET @UpdateQuery = REPLACE(@Query,'@ID', CAST(@ID AS VARCHAR));
		EXEC(@UpdateQuery);
		FETCH NEXT FROM TableRows INTO @ID;
END

CLOSE TableRows;
DEALLOCATE TableRows;

/*done!*/
------------------------------------------------------------------

Open in new window

Goodangel MatopeSoftware ArchitectCommented:
Oops, sorry that was an incomplete answer, because it only sets the NULLS to empty strings. But you can then create the defaults by running this script, and running the resultant query which will be in the messages tab

DECLARE @ColumnName VARCHAR(100);
DECLARE @Query VARCHAR(MAX);
DECLARE @UpdateQuery VARCHAR(MAX);
DECLARE @TableName VARCHAR(100);

DECLARE @ID INT;

SET @TableName = 'tblClient'

DECLARE StringColumns CURSOR FOR
SELECT [COLUMN_NAME]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'' + @TableName + ''
AND DATA_TYPE IN ('varchar','char','nvarchar','text','ntext')
AND COLUMN_NAME = 'Name3'

OPEN StringColumns;
FETCH NEXT FROM StringColumns INTO @ColumnName;

SET @Query = '
';

WHILE @@FETCH_STATUS = 0
BEGIN

		SET @Query = @Query + 'CREATE DEFAULT df' + @TableName + @ColumnName + ' AS '''';
		GO
		';
		
		SET @Query = @Query + 'EXEC sp_bindefault ''df' + @TableName + @ColumnName + ''', ''dbo.' + @TableName + '.' + @ColumnName + ''';
		GO
		';

		FETCH NEXT FROM StringColumns INTO @ColumnName;

END

CLOSE StringColumns;
DEALLOCATE StringColumns;

PRINT @Query;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
The code below does a single UPDATE for each table, rather than 1 column at a time.  That should run drastically faster, esp. on large tables.

There is one ALTER statement per column change, since only a single column can be altered in one statement.

The script has a flag that controls whether the code actually executes or is just generated.  After you're reviewed the generated code, you can set the exec bit on to actually run the code.


DECLARE @print_sql bit
DECLARE @exec_sql bit

SET @print_sql = 1
SET @exec_sql = 0

IF OBJECT_ID('tempdb..#sql_cmds') IS NOT NULL
    DROP TABLE #sql_cmds
CREATE TABLE #sql_cmds (
    schema_name sysname NOT NULL,
    table_name sysname NOT NULL,
    update_columns nvarchar(max) NULL,
    alter_columns nvarchar(max) NULL,
    UNIQUE CLUSTERED ( schema_name, table_name )
    )

INSERT INTO #sql_cmds ( schema_name, table_name, update_columns, alter_columns )
SELECT SCHEMA_NAME(t.schema_id) AS schema_name, t.name AS table_name, ca1.*, ca2.*
FROM sys.tables t
CROSS APPLY (
    SELECT
        ', [' + c.name + '] = ISNULL([' + c.name + '], '''')'
    FROM sys.columns c WITH (NOLOCK)
    INNER JOIN sys.types ty WITH (NOLOCK) ON
        ty.system_type_id = c.system_type_id AND
        ty.user_type_id = c.user_type_id AND
        ty.name LIKE '%char%'        
    WHERE
        c.object_id = t.object_id AND        
        c.max_length > -1 AND
        c.is_nullable = 1
    ORDER BY c.column_id
    FOR XML PATH('')
) AS ca1 ( sql_cmd_update_columns )
CROSS APPLY (
    SELECT
        '; ' +
        'ALTER TABLE [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] '  +
        'ALTER COLUMN [' + c.name + '] ' +
        LOWER(ty.name) + '(' + CAST(c.max_length AS varchar(10)) + ') ' +
        'NOT NULL; ' +
        'ALTER TABLE [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] '  +
        'ADD DEFAULT '''' FOR [' + c.name + ']'
    FROM sys.columns c WITH (NOLOCK)
    INNER JOIN sys.types ty WITH (NOLOCK) ON
        ty.system_type_id = c.system_type_id AND
        ty.user_type_id = c.user_type_id AND
        ty.name LIKE '%char%'        
    WHERE
        c.object_id = t.object_id AND        
        c.max_length > -1 AND
        c.is_nullable = 1
    ORDER BY c.column_id
    FOR XML PATH('')
) AS ca2 ( sql_cmd_alter_columns )
WHERE
    ca1.sql_cmd_update_columns IS NOT NULL
ORDER BY
    schema_name, table_name

UPDATE #sql_cmds
SET update_columns = 'UPDATE [' + schema_name + '].[' + table_name + '] SET ' + STUFF(update_columns, 1, 2, '')
WHERE
    update_columns NOT LIKE 'UPDATE%'

SELECT *
FROM #sql_cmds


DECLARE cursor_tables CURSOR FAST_FORWARD FOR
SELECT schema_name, table_name, update_columns, alter_columns
FROM #sql_cmds
ORDER BY schema_name, table_name

DECLARE @schema_name sysname
DECLARE @table_name sysname
DECLARE @update_columns nvarchar(max)
DECLARE @alter_columns nvarchar(max)

OPEN cursor_tables

WHILE 1 = 1
BEGIN
    FETCH NEXT FROM cursor_tables INTO @schema_name, @table_name, @update_columns, @alter_columns
    IF @@FETCH_STATUS = -1
        BREAK
    IF @@FETCH_STATUS = -2
        CONTINUE
    IF @print_sql = 1
    BEGIN
        PRINT @schema_name + '.' + @table_name
        PRINT @update_columns
        PRINT @alter_columns
    END
    IF @exec_sql = 1
    BEGIN
        EXEC (@update_columns)
        EXEC (@alter_columns)
    END
END --WHILE

DEALLOCATE cursor_tables
SamCashAuthor Commented:
Long weekend, but they both did the job!  Thanks much!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.