October 5, 2020 at 2:22 pm
there is a parameter in stored procedure, and this parameter is table variable, How to get all the column name and data type for a table variable? thanks!
October 5, 2020 at 2:36 pm
If it is a table variable being passed in, then it is a User Defined Table Type. In SSMS, open Programmability then Types under the particular database
For better, quicker answers, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
October 5, 2020 at 2:47 pm
SELECT TableVariableTypeName = tt.name
, ColumnName = c.name
, ColumnNumber = c.column_id
, [DataType] = dt.name
+ CASE WHEN dt.name IN ('datetime2', 'datetimeoffset')
THEN '(' + CONVERT(VARCHAR, c.scale) + ')'
WHEN dt.name IN ('char', 'varchar', 'binary', 'varbinary')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR, c.max_length) END + ')'
WHEN dt.name IN ('nchar', 'nvarchar')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR, c.max_length/2) END + ')'
WHEN dt.name IN ('decimal', 'numeric')
THEN '(' + CONVERT(VARCHAR, c.[precision]) + ', ' + CONVERT(VARCHAR, c.scale) + ')'
ELSE ''
END
FROM sys.table_types AS tt
INNER JOIN sys.all_columns AS c ON tt.type_table_object_id = c.object_id
INNER JOIN sys.types dt ON dt.user_type_id = c.user_type_id
WHERE tt.is_user_defined = 1
AND tt.is_table_type = 1
AND tt.name = ??? -- Add your table variable type name here
ORDER BY c.column_id;
October 7, 2020 at 2:40 am
SELECT TableVariableTypeName = tt.name
, ColumnName = c.name
, ColumnNumber = c.column_id
, [DataType] = dt.name
+ CASE WHEN dt.name IN ('datetime2', 'datetimeoffset')
THEN '(' + CONVERT(VARCHAR, c.scale) + ')'
WHEN dt.name IN ('char', 'varchar', 'binary', 'varbinary')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR, c.max_length) END + ')'
WHEN dt.name IN ('nchar', 'nvarchar')
THEN '(' + CASE WHEN c.max_length = -1 THEN 'max' ELSE CONVERT(VARCHAR, c.max_length/2) END + ')'
WHEN dt.name IN ('decimal', 'numeric')
THEN '(' + CONVERT(VARCHAR, c.[precision]) + ', ' + CONVERT(VARCHAR, c.scale) + ')'
ELSE ''
END
FROM sys.table_types AS tt
INNER JOIN sys.all_columns AS c ON tt.type_table_object_id = c.object_id
INNER JOIN sys.types dt ON dt.user_type_id = c.user_type_id
WHERE tt.is_user_defined = 1
AND tt.is_table_type = 1
AND tt.name = ??? -- Add your table variable type name here
ORDER BY c.column_id;
as the screenshot, I declare a table variable, but it seems to be not working
October 7, 2020 at 7:24 am
That code will not work for a declared table variable.
It is for a table parameter as requested in the original post.
Also note that the code is looking for the declared UserDefinedType, not the parameter name.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply