Wednesday, November 14, 2012

How to update all varchar, nvarchar or char field of a SQL Server database and set empty string in the place of NULL?


declare @query varchar(MAX)
SET @query='';
select @query=@query+' UPDATE '+table_name+' SET '+column_name+' = isnull('+column_name+','''');'
from (
SELECT table_name=sysobjects.name,
column_name
=syscolumns.name
FROM sysobjects
JOIN syscolumns ON sysobjects.id = syscolumns.id
JOIN systypes ON syscolumns.xtype=systypes.xtype
WHERE sysobjects.xtype='U' and (systypes.name='nvarchar' or systypes.name='varchar' or systypes.name='char')
) as a

exec (@query)

No comments:

Post a Comment