Omit Data Length for Numeric Values

  • Hello Everyone

    I am merely playing around with an idea today. It is slow today, so I needed to teach myself something new. But as with some things, I have ran into a snag. I could do this manually, but that never any fun.

    I am executing a select query that returns the column names and the data type for a single table. I am using a system table in the MSDB for sample purpose, since we all have an MSDB database to use.

    SELECT

    c.name AS ColumnName

    , t.name+'('+CAST(c.max_length AS varchar(3))+')' AS ColumnDataType

    FROM

    sys.all_columns c

    JOIN

    sys.types t

    ON

    c.user_type_id

    = t.user_type_id

    WHERE object_id =

    (SELECT object_id

    FROM sys.tables

    WHERE name = 'backupset')

    ORDER BY c.column_id ASC

    That will return this resultset:

    ColumnNameColumnDataType

    backup_set_idint(4)

    backup_set_uuiduniqueidentifier(16)

    media_set_idint(4)

    first_family_numbertinyint(1)

    first_media_numbersmallint(2)

    last_family_numbertinyint(1)

    last_media_numbersmallint(2)

    catalog_family_numbertinyint(1)

    catalog_media_numbersmallint(2)

    positionint(4)

    expiration_datedatetime(8)

    software_vendor_idint(4)

    namenvarchar(256)

    descriptionnvarchar(510)

    user_namenvarchar(256)

    software_major_versiontinyint(1)

    software_minor_versiontinyint(1)

    software_build_versionsmallint(2)

    time_zonesmallint(2)

    mtf_minor_versiontinyint(1)

    first_lsnnumeric(13)

    last_lsnnumeric(13)

    checkpoint_lsnnumeric(13)

    database_backup_lsnnumeric(13)

    database_creation_datedatetime(8)

    backup_start_datedatetime(8)

    backup_finish_datedatetime(8)

    I have shortened the resultset by a few rows. I would like to be able to not show the max_length of the data type for all non-character data types.

    So for example:

    ColumnNameColumnDataType

    backup_set_idint

    backup_set_uuiduniqueidentifier

    media_set_idint

    database_namenvarchar(256)

    server_namenvarchar(256)

    machine_namenvarchar(256)

    last_media_numbersmallint

    catalog_family_numbertinyint

    catalog_media_numbersmallint

    positionint

    expiration_datedatetime

    software_vendor_idint

    namenvarchar(256)

    descriptionnvarchar(510)

    user_namenvarchar(256)

    I have tried a CASE statement in the JOIN clause, that only limits the rows returned.

    Does anyone have an idea as to how to correctly make this work?

    Thank you in advance for all your assistance, suggestions and comments

    Andrew SQLDBA

  • This comes close:

    SELECT

    c.name AS ColumnName

    , t.name + CASE WHEN (c.precision <> 0 OR c.scale <> 0)

    THEN ''

    ELSE '('+CAST(c.max_length AS varchar(3))+')'

    END AS ColumnDataType

    ,c.precision

    ,c.scale

    FROM

    sys.all_columns c

    JOIN

    sys.types t

    ON

    c.user_type_id

    = t.user_type_id

    WHERE object_id =

    (SELECT object_id

    FROM sys.tables

    WHERE name = 'backupset')

    ORDER BY c.column_id ASC;

    It does show the maximum length for a uniqueidentifier, which technically holds characters 🙂

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • I think I'd do this:

    SELECT

    c.name AS ColumnName,

    t.name + CASE WHEN T.NAME LIKE '%char%'

    THEN CASE WHEN C.max_length > 0 THEN '(' + CAST(c.max_length AS VARCHAR(3)) + ')'

    ELSE '(max)'

    END

    WHEN T.NAME LIKE '%binary' AND

    C.max_length > 0 THEN '(' + CAST(c.max_length AS VARCHAR(3)) + ')'

    ELSE ''

    END AS ColumnDataType,

    c.precision,

    c.scale

    FROM

    sys.all_columns c

    JOIN sys.types t

    ON c.user_type_id = t.user_type_id

  • You have to go further than that, including considerations of datetime, datetime2, etc..

    NOTE: The code below does NOT consider any user-defined data types.

    -- list column data type, including len(s), if applicable

    CASE WHEN c.is_computed = 1 THEN 'AS ' +

    (SELECT definition FROM sys.computed_columns cc WHERE cc.object_id = c.object_id AND cc.column_id = c.column_id) +

    CASE WHEN c.is_nullable = 0 THEN ' NOT NULL' ELSE '' END

    ELSE t.name + CASE

    WHEN t.name LIKE '%bin%' OR t.name LIKE '%char%' THEN

    '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS varchar(10)) + ')' END

    WHEN t.name IN ('datetime2', 'time') THEN

    '(' + CAST(c.scale AS varchar(3)) + ')'

    WHEN t.name IN ('decimal', 'numeric') THEN

    '(' + CAST(c.precision AS varchar(3)) + ', ' + CAST(c.scale AS varchar(3)) + ')'

    WHEN t.name IN ('float', 'real') THEN

    '(' + CAST(c.precision AS varchar(3)) + ')'

    ELSE '' END +

    CASE WHEN c.is_nullable = 0 THEN ' NOT' ELSE '' END + ' NULL'

    END

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Here is a query that I have canned. It's part of a tool, and the purpose is to return information about all user-defined types. (But I realised today that it fails to return information about CLR types and table type).

    The gist is fairly close to Scott's query, although he had forgotten datetimeoffset. And he had to failed to adjust the length for nchar/nvarchar. A complete query should also cover xml columns with a schema collection. I see now that my query does not handle MAX columns.

    SELECT typename = CASE N.N WHEN 1 THEN lower(a.name)

    WHEN 2 THEN lower(s.name) + '.' + lower(a.name)

    END,

    typedef = b.name +

    CASE WHEN b.name IN ('nchar', 'nvarchar')

    THEN '(' + ltrim(str(a.max_length / 2)) + ')'

    WHEN b.name IN ('char', 'varchar', 'binary', 'varbinary')

    THEN '(' + ltrim(str(a.max_length)) + ')'

    WHEN b.name IN ('decimal', 'numeric')

    THEN '(' + ltrim(str(a.precision)) + ',' +

    ltrim(str(a.scale)) + ')'

    WHEN b.name IN ('datetime2', 'time', 'datetimeoffset')

    THEN '(' + ltrim(str(a.scale)) + ')'

    ELSE ''

    END

    FROM sys.types a

    JOIN sys.types b ON a.system_type_id = b.system_type_id

    JOIN sys.schemas s ON a.schema_id = s.schema_id

    CROSS JOIN (SELECT N = 1 UNION ALL SELECT 2) AS N

    WHERE a.user_type_id <> a.system_type_id

    AND b.user_type_id = b.system_type_id

    ORDER BY a.name

    [font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]

  • Good points about the Unicode and datetimeoffset (I've never personally used that data type):

    -- list column data type, including len(s), if applicable

    CASE WHEN c.is_computed = 1 THEN 'AS ' +

    (SELECT definition FROM sys.computed_columns cc WHERE cc.object_id = c.object_id AND cc.column_id = c.column_id) +

    CASE WHEN c.is_nullable = 0 THEN ' NOT NULL' ELSE '' END

    ELSE t.name + CASE

    WHEN t.name LIKE '%n%char%' THEN

    '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length / 2 AS varchar(10)) + ')' END

    WHEN t.name LIKE '%bin%' OR t.name LIKE '%char%' THEN

    '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS varchar(10)) + ')' END

    WHEN t.name IN ('datetime2', 'time', 'timeoffset') THEN

    '(' + CAST(c.scale AS varchar(3)) + ')'

    WHEN t.name IN ('decimal', 'numeric') THEN

    '(' + CAST(c.precision AS varchar(3)) + ', ' + CAST(c.scale AS varchar(3)) + ')'

    WHEN t.name IN ('float', 'real') THEN

    '(' + CAST(c.precision AS varchar(3)) + ')'

    ELSE '' END +

    CASE WHEN c.is_nullable = 0 THEN ' NOT' ELSE '' END + ' NULL'

    END

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply