MS SQL 2005 column data type

[flynn]

I am tasked with creating a generic "end-user" query tool. One of the features is the ability to query on any field of any table. I have most of my design done, but I still need the ability to determine if a column is based on a "number" type. The type can any of the "number" types: tinyint, bigint, numeric, decimal, money, etc. Basically any column that can be compared using "<", ">" or "=" operators.

 

Using "exec sp_columns 'customer'", I can get a list of the columns and data types (column "TYPE_NAME"), but I was hoping that there was an easier way to determine what columns I can apply the "<>=" operators.

 

If there isn't then I guess I'm stuck with looping through the column types to determine if the selected column can be used as a "number" type.

 

Typical use would be allowing the end-user to select a column from a list of available columns, then select an operator (<>=), then type a number in a text box to use as the compared value.

 

tia,

flynn