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
Friday, September 13, 2024
Monday, June 17, 2024
SQL documentation, updated Jun 2024
SELECT T.name AS TableName
, C.colorder AS ColOrder
, C.name AS ColName
, CASE WHEN C.xtype = 34 THEN 'IMAGE'
WHEN C.xtype = 35 THEN 'TEXT'
WHEN C.xtype = 36 THEN 'UNIQUEIDENTIFIER'
WHEN C.xtype = 40 THEN 'DATE'
WHEN C.xtype = 41 THEN 'TIME('
+ CONVERT(VARCHAR,C.xscale) + ')'
WHEN C.xtype = 42 THEN 'DATETIME2('
+ CONVERT(VARCHAR,C.xscale) + ')'
WHEN C.xtype = 48 THEN 'TINYINT'
WHEN C.xtype = 52 THEN 'SMALLINT'
WHEN C.xtype = 56 THEN 'INT'
WHEN C.xtype = 58 THEN 'SMALLDATETIME'
WHEN C.xtype = 59 THEN 'REAL'
WHEN C.xtype = 60 THEN 'MONEY'
WHEN C.xtype = 61 THEN 'DATETIME'
WHEN C.xtype = 62 THEN 'FLOAT'
WHEN C.xtype = 98 THEN 'SQL_VARIANT'
WHEN C.xtype = 99 THEN 'NTEXT'
WHEN C.xtype = 104 THEN 'BIT'
WHEN C.xtype = 106 THEN 'DECIMAL('
+ CONVERT(VARCHAR,C.xprec) + ','
+ CONVERT(VARCHAR,C.xscale)+ ')'
WHEN C.xtype = 108 THEN 'NUMERIC('
+ CONVERT(VARCHAR,C.xprec) + ','
+ CONVERT(VARCHAR,C.xscale)+ ')'
WHEN C.xtype = 122 THEN 'SMALLMONEY'
WHEN C.xtype = 127 THEN 'BIGINT'
WHEN C.xtype = 165 THEN 'VARBINARY'
WHEN C.xtype = 167 THEN
CASE WHEN C.prec>0
THEN 'VARCHAR('
+ CONVERT(VARCHAR,C.prec) + ')'
ELSE 'VARCHAR(MAX)' END
WHEN C.xtype = 173 THEN 'BINARY('
+ CONVERT(VARCHAR,C.prec) + ')'
WHEN C.xtype = 175 THEN 'CHAR('
+ CONVERT(VARCHAR,C.prec) + ')'
WHEN C.xtype = 189 THEN 'TIMESTAMP'
WHEN C.xtype = 231 THEN
CASE WHEN C.prec>0
THEN 'NVARCHAR('
+ CONVERT(VARCHAR,C.prec) + ')'
ELSE 'NVARCHAR(MAX)' END
WHEN C.xtype = 239 THEN 'NCHAR('
+ CONVERT(VARCHAR,C.prec) + ')'
ELSE '??? ' + CONVERT(VARCHAR,C.xtype) + ' ???' END
AS ColType
, C.isnullable AS ColNullable
FROM sysobjects T
INNER JOIN syscolumns C On C.ID = T.ID
WHERE T.xtype = 'U'
ORDER BY T.Name, C.ColOrder
Subscribe to:
Posts (Atom)