I have a computed column which uses a Scalar-valued Function to evaluate it's value. I need to make the column Persisted because of some reasons and to do it, I need to make that function deterministic. I use a query like the following to get the column type:
SELECT
@dataType = DATA_TYPE
FROM
INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE
TABLE_SCHEMA = 'X' AND
TABLE_NAME = @TableName AND
COLUMN_NAME = @ColumnName
When I try to add WITH SCHEMABINDING clause to the function (to make it deterministic) I get the following error:
Cannot schema bind function 'X.MY_FUNCTION' because it references system object 'INFORMATION_SCHEMA.COLUMNS'.
How can I get the type of a column in a deterministic way? I looked into COLUMNPROPERTY and other Metadata functions but couldn't find a way to get the type of a column without using system objects.