Abu Dina (5/23/2013)
Crude but quick way of doing this is to copy then run the outcome of a query like the below:
select 'update ' + table_name +'set ' + column_name + ' = NULL
where ISNUMERIC(' + column_name + ') = 1 AND cast(' + column_name + ' as int) < 0'
from INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE = 'varchar'
arrghhh I LOVE these views I don't know why MS is getting riod off them! :crazy:
Actually this will probably not work with columns that have mixed data as the cast would fail. hmmmm
I agree about those views. The columns one is particularly helpful. I suspect that there will be many people who will end up writing their own as a replacement. 😉
I modified your code slightly so it will also capture empty strings.
select 'update ' + table_name +
' set ' + column_name + ' = NULL
where (ISNUMERIC(' + column_name + ') = 1 AND cast(' + column_name + ' as int) < 0) or ' + COLUMN_NAME + ' = '''''
from INFORMATION_SCHEMA.COLUMNS
where DATA_TYPE = 'varchar'
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/