Skip to content

Instantly share code, notes, and snippets.

@kevindb
Last active August 29, 2015 14:18
Show Gist options
  • Save kevindb/4713383b69dac8dfb6d4 to your computer and use it in GitHub Desktop.
Save kevindb/4713383b69dac8dfb6d4 to your computer and use it in GitHub Desktop.
Sets empty string columns to NULL in a SQL Server table
SET NOCOUNT ON;
DECLARE @TABLE VARCHAR(100),
@SQL VARCHAR(MAX);
SET @TABLE = '';
SELECT @SQL = COALESCE(@SQL, '')
+ CAST( 'UPDATE ' + @TABLE + ' ' +
'SET [' + C.COLUMN_NAME + '] = NULL ' +
'WHERE [' + C.COLUMN_NAME + '] = ''''; ' + Char(13)
AS VARCHAR(MAX))
FROM INFORMATION_SCHEMA.COLUMNS C
JOIN sysobjects B
ON C.TABLE_NAME = B.NAME
WHERE C.TABLE_NAME = @TABLE
AND C.DATA_TYPE IN ('varchar','nvarchar')
AND C.IS_NULLABLE = 'YES'
AND B.xtype = 'U';
EXEC(@SQL);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment