I am developing one automation tool for data migration from one table to other table, here i am looking for one function or SP for which i will pass source column and destination column as input parameter and want output parameter to return true when source column data is compatible to copy to destination column if not then it should return false.
For example if source column is varchar and destination column is integer, the script should check all the data in source column in good enough to move to integer column or not and return the output flag. I want a script to work this for all types of data types. Any suggestions it will be helpful.
You'll need to reference the sys.syscolumns table and the column type value, and cross-reference that against a table of compatibility. Then you'll also need to check the column width for character data and the precision level for numeric data, as well as check for allowance of NULL values and constraints on the target column.
If it's not practically useful, then it's practically useless.