Use the ISNUMERIC function of the sql server to this job.

To check all records of that particular column if it contains a string or can be converted to numeric use the script below.

select
case when sum(
case when COLUMN_NAME is null then 1
when ltrim(rtrim(COLUMN_NAME)) = ” then 1
else isnumeric(COLUMN_NAME)
end) = count(*) then 1 else 0 end x from TABLENAME

You can find more info here