Tuesday, September 20, 2016

SQL documentation, updated

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 =  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

No comments:

Post a Comment