Deterministic way to get column type

Question

I have a calculated column that uses the Scalar-value Function to evaluate its value. I need to make the Persisted column for some reason and to do this, I need to make this function deterministic. To get the column type, I use the following query:

 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 a WITH SCHEMABINDING to a function (to make it deterministic), I get the following error:

The schema function 'X.MY_FUNCTION' cannot be bound because it references the system object 'INFORMATION_SCHEMA.COLUMNS'.

How can I get the column type in a deterministic way? I looked at COLUMNPROPERTY and other metadata functions, but couldn't find a way to get the column type without using system objects.

Answer 1

For TSQL, TVF SQL verifies that the function is deterministic, but SQL Fails to verify that your function is determinate if it accesses the directory views because it cannot use SCHEMABINDING.

You can use CLR TVF, which SQL does not fully validate, but allows you to mark CLR TVF as deterministic and use it in a constant computed column.

Answer 2

You can try something like this, but I'm not sure if this will help ...

 SELECT o.name,c.name,t.name,c.max_length,c.precision,c.is_nullable FROM sys.columns AS c INNER JOIN sys.objects AS o ON c.object_id=o.object_id INNER JOIN sys.types AS t ON c.system_type_id=t.system_type_id WHERE o.name=@YourTableName AND c.name=@YourColumnName 

UPDATE Worst case: -D

No one has yet given an answer ... There seems to be no general approach ...

So think about it:

 DECLARE @cmd VARCHAR(MAX); WITH AllColumns AS ( SELECT c.TABLE_NAME AS tbl ,c.COLUMN_NAME AS col ,c.DATA_TYPE AS tp FROM INFORMATION_SCHEMA.COLUMNS AS c ) SELECT @cmd=REPLACE ('CASE @tablename ' + (SELECT ' WHEN ''' + c1.tbl + ''' THEN \n' +( SELECT 'CASE @columnname \n' + ( SELECT ' WHEN ''' + c2.col + ''' THEN ''' + c2.tp + '''\n' FROM AllColumns AS c2 WHERE c2.tbl=c1.tbl FOR XML PATH('') ) + ' END' ) FROM AllColumns AS c1 GROUP BY c1.tbl FOR XML PATH('')) + ' END','\n',CHAR(13)+CHAR(10)); --DROP FUNCTION GetColumnType; SET @cmd='CREATE FUNCTION GetColumnType(@tablename VARCHAR(250),@columnname VARCHAR(250)) ' + CHAR(13) + CHAR(10) + 'RETURNS TABLE AS RETURN ' + CHAR(13) + CHAR(10) + 'SELECT @tablename AS TableName,@columnname AS ColumnName,' + @cmd + ' AS DataType;'; EXEC(@cmd); GO SELECT * FROM GetColumnType('SomeTable','SomeColumn'); 

This will create an embedded TVF with all the types you need. Whenever you change the database structure, you run this script (first with DROP FUNCTION ), and everything is fine again.