MrDee

Wednesday, September 25, 2024

SSMS and Cascadia Ligatures

TIL: Ligatures are a thing in code editors.

According to Google, "A ligature is a glyph that combines the shapes of certain sequences of characters into a new form that makes for a more harmonious reading experience."

The new (to me) font "Cascadia" has variants, some with and some without ligatures. (see below) It is an upgrade from Consolas, the previous Microsoft developers font.

Why are there multiple versions? This is because some people are "show me the actual characters" sort of people and others are "Make it pretty" sort of people. For prose I like ligatures, for programming I need precision. If you need to squint to tell the difference between >= and <= then it has gone too far. Also editing them is wierd, having the cursor/insertion point half way through a character feels wrong.

Final note, SQL is not C#. Take your non-ANSI != and keep it out of my SQL.

Friday, September 13, 2024

SQL Column Type , updated Sep 2024

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

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