2

I'm writing a table valued function in SQL Server Data Tools (SSDT). Table valued functions require you to explicitly state the data type of the returned table. In my particular case, I've written a query which returns the values that I want. I can execute it using SSDT, but can't figure out how to quickly determine the data type of the columns returned.

Is there a way to get the list of columns that it returns and the data types of said columns so I can quickly and accurately populate the list of columns in the ReturnTable?

The closest I've gotten is to hover over the field name in the SELECT clause, but that doesn't provide the length or precision limitations.

enter image description here

Edit: I struck through my comment regarding table valued functions require you to explicitly state the data types of the columns in the returned table based on @srutkzy's answer. Thanks!

2 Answers 2

1

Table valued functions require you to explicitly state the data type of the returned table.

That statement is not necessarily correct. Yes, multi-statement TVFs do require that you declare the return table. However, inline TVFs do not require any table definition as they simply encapsulate a single query (essentially a View that accepts input parameters). Please see the MSDN page for CREATE FUNCTION for more details.

Given that inline TVFs are much more efficient than multi-statement TVFs, the first question is: can this query be done in an inline TVF? If yes, then make it an inline TVF and then there is no need to define the table structure.

If the operation cannot be done as an inline TVF, then you should get the datatypes from the base table(s). You can use either:

EXEC sp_help 'SchemaName.TableName';
-- for NVARCHAR / NCHAR, use Length/2 to get the size

or:

SELECT col.*
FROM   INFORMATION_SCHEMA.COLUMNS col
WHERE  col.TABLE_SCHEMA = N'SchemaName'
AND    col.TABLE_NAME = N'TableName';
-- for NVARCHAR / NCHAR, use CHARACTER_MAXIMUM_LENGTH to get the size
1

You can use the SELECT..INTO syntax to create a table on-the-fly and examine the resultant column definitions in sys.columns.

select
  *
into #SomeName
from <your query goes here>
where 1=0 -- because we only want the metadata

I think sys.columns has definitons for functions, too, but I don't have an instance to hand just now to check. Alternately you could create a view with the same query you are about to put into your function and check the catalogue for that.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.