CREATE OR ALTER FUNCTION [ddt].[fn_ColumnType]
( @TableName VARCHAR(50) NULL
, @ColumnName VARCHAR(50) NULL
)
RETURNS VARCHAR(100)
AS
BEGIN
-- Declare the return variable here
DECLARE @ColumnType VARCHAR(100)
SELECT @ColumnType =
CASE WHEN C.system_type_id = 34 THEN 'IMAGE'
WHEN C.system_type_id = 35 THEN 'TEXT' -- deprecated, use VARCHAR(MAX)
WHEN C.system_type_id = 36 THEN 'UNIQUEIDENTIFIER'
WHEN C.system_type_id = 40 THEN 'DATE'
WHEN C.system_type_id = 41 THEN 'TIME(' + CONVERT(VARCHAR(10),C.scale) + ')'
WHEN C.system_type_id = 42 THEN 'DATETIME2(' + CONVERT(VARCHAR(10),C.scale) + ')'
WHEN C.system_type_id = 48 THEN 'TINYINT'
WHEN C.system_type_id = 52 THEN 'SMALLINT'
WHEN C.system_type_id = 56 THEN 'INT'
WHEN C.system_type_id = 58 THEN 'SMALLDATETIME'
WHEN C.system_type_id = 59 THEN 'REAL'
WHEN C.system_type_id = 60 THEN 'MONEY'
WHEN C.system_type_id = 61 THEN 'DATETIME'
WHEN C.system_type_id = 62 THEN 'FLOAT'
WHEN C.system_type_id = 98 THEN 'SQL_VARIANT'
WHEN C.system_type_id = 99 THEN 'NTEXT' -- deprecated, use NVARCHAR(MAX)
WHEN C.system_type_id = 104 THEN 'BIT'
WHEN C.system_type_id = 106 THEN 'DECIMAL(' + CONVERT(VARCHAR(10),C.precision) + ','+ CONVERT(VARCHAR(10),C.scale)+ ')'
WHEN C.system_type_id = 108 THEN 'NUMERIC(' + CONVERT(VARCHAR(10),C.precision) + ','+ CONVERT(VARCHAR(10),C.scale)+ ')'
WHEN C.system_type_id = 122 THEN 'SMALLMONEY'
WHEN C.system_type_id = 127 THEN 'BIGINT'
WHEN C.system_type_id = 165 THEN 'VARBINARY'
WHEN C.system_type_id = 167 THEN
CASE WHEN C.max_length>0
THEN 'VARCHAR(' + CONVERT(VARCHAR(10),C.max_length) + ')'
ELSE 'VARCHAR(MAX)' END
WHEN C.system_type_id = 173 THEN 'BINARY(' + CONVERT(VARCHAR(10),C.precision) + ')'
WHEN C.system_type_id = 175 THEN 'CHAR(' + CONVERT(VARCHAR(10),C.max_length) + ')'
WHEN C.system_type_id = 189 THEN 'TIMESTAMP'
WHEN C.system_type_id = 231 THEN
CASE WHEN C.max_length>0
THEN 'NVARCHAR(' + CONVERT(VARCHAR(10),C.max_length/2) + ')' -- NVARCHAR has 2 byte length characters
ELSE 'NVARCHAR(MAX)' END
WHEN C.system_type_id = 239 THEN 'NCHAR(' + CONVERT( VARCHAR(10),C.max_length/2) + ')' -- NCHAR has 2 byte length characters
ELSE '???( ' + CONVERT( VARCHAR(10),C.system_type_id) + ')'
END
FROM sys.tables T
INNER JOIN sys.columns C ON C.object_id = T.object_id
WHERE T.type = 'U'
AND T.name = @TableName
AND C.name = @ColumnName;
RETURN @ColumnType
END
No comments:
Post a Comment