SQL get table columns with type name

Question

I have successfully getting the information of tables and columns from below query.

Is there any handy way to get the type name with the description like nvarchar(20), numeric(14,2), varchar(max) etc. instead of lot's of CASE statements?

SELECT 
    o.name AS TableName,
    c.name AS ColumnName,
    t.name + 
    CASE 
        WHEN t.name LIKE '%char' THEN '(' + 
            CASE 
                WHEN c.max_length = -1 THEN 'max' 
                ELSE CONVERT(varchar(10), c.max_length / CASE WHEN t.name LIKE 'n%' THEN 2 ELSE 1 END) 
            END + ')'
        WHEN t.name IN ('numeric', 'decimal') THEN '(' + CONVERT(varchar(4), c.precision) + ',' + CONVERT(varchar(4), c.scale) + ')'
        -- WHEN .... many other types
        ELSE ''
    END AS TypeName
FROM 
    sys.objects o 
    INNER JOIN sys.columns c ON o.object_id = c.object_id
    INNER JOIN sys.types t ON t.system_type_id = c.system_type_id AND t.user_type_id = c.user_type_id
WHERE
    o.is_ms_shipped = 0
ORDER BY
    o.name,
    c.column_id

Edit

sp_help nor the information schema return the name not being like nvarchar(20), numeric(14,2), varchar(max)

possible duplicate of [T-SQL query to show table definition?](http://stackoverflow.com/questions/6215459/t-sql-query-to-show-table-definition) — Anil, Jul 17 '15 at 02:18
I'm pretty sure that you're stuck with the case expressions as there is no system view that presents the information in the format you want as far as I know. — jpw, Jul 17 '15 at 02:27

score 0 · Answer 1 · answered Jul 17 '15 at 02:25

I use this view to Document the Database.... you could used this and use the data_type and Length/Precision

/****** Object:  View [dbo].[vw_DataDictionary]    Script Date: 17/07/2015 10:24:04 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[vw_DataDictionary]
AS
SELECT     TOP (100) PERCENT tb.name AS Table_Name, CAST(ept.value AS nvarchar(200)) AS Table_Description, CAST(c.name AS nvarchar(200)) AS Column_Name, 
                      CAST(ep.value AS nvarchar(200)) AS column_Description, t.name AS data_type, c.is_nullable AS Is_Null, object_definition(c.default_object_id) AS default_text, 
                      c.max_length AS Length, c.precision AS numeric_precision, c.scale AS numeric_scale, c.column_id
FROM         sys.columns AS c LEFT OUTER JOIN
                      sys.tables AS tb ON tb.object_id = c.object_id LEFT OUTER JOIN
                      sys.types AS t ON c.system_type_id = t.system_type_id AND c.user_type_id = t.user_type_id LEFT OUTER JOIN
                      sys.extended_properties AS ept ON ept.major_id = tb.object_id AND ept.minor_id = 0 AND ept.name = 'MS_Description' LEFT OUTER JOIN
                      sys.extended_properties AS ep ON ep.major_id = c.object_id AND ep.minor_id = c.column_id AND ep.name = 'MS_Description'
WHERE     (tb.type = 'U') AND (tb.name <> 'sysdiagrams')
ORDER BY Table_Name, c.column_id

GO

Thank you, but it seems that I still need to format the type like the way in the question — Eric, Jul 17 '15 at 02:33

Eric · Answer 2 · 2015-10-14T06:38:50.190

For SQL Server 2008 R2, I have declared the type by CASE as below:

SQL Fiddle

SELECT 
    DB_NAME() AS DatabaseName, 
    SCHEMA_NAME(o.schema_id) AS SchemaName, 
    o.name AS ObjectName, 
    c.name AS ColumnName, 
    RTRIM(o.type) AS ObjectType, 
    t.name AS TypeName,
    c.max_length AS Length,

    -- The logic is implemented in this column
    t.name + 
    CASE 
        -- (max) for char type / binary only
        WHEN (t.name LIKE '%char%' OR t.name LIKE '%binary') AND c.max_length = -1 THEN '(max)'
        -- (n) where n = length / 2 for nchar and nvarchar
        WHEN t.name LIKE '%char%' AND LEFT(t.name, 1) = 'n' THEN '(' + CONVERT(varchar, c.max_length / 2) + ')' 
        -- (n) where n = length
        WHEN t.name LIKE '%char%' OR t.name LIKE '%binary' THEN '(' + CONVERT(varchar, c.max_length) + ')' 
        -- (p,s) where p = precision, s = scale
        WHEN t.name IN ('numeric', 'decimal') THEN '(' + CONVERT(varchar, c.precision) + ',' + CONVERT(varchar, c.scale) + ')'
        -- (s) where s = scale
        WHEN t.name IN ('time', 'datetime2', 'datetimeoffset') THEN '(' + CONVERT(varchar, c.scale) + ')'
        ELSE '' 
    END AS Type
FROM 
    sys.objects o 
    inner join sys.columns c on o.object_id = c.object_id
    inner join sys.types t on c.system_type_id = t.system_type_id and c.user_type_id = t.user_type_id
WHERE 1 = 1
    AND o.is_ms_shipped = 1

score 0 · Answer 3 · edited May 23 '17 at 11:58

This should help you, Adopted from here

DECLARE @tblname VARCHAR(300)

SET @tblname = 'tblname'

SELECT o.list AS ColumnsDescription
FROM sysobjects so
CROSS APPLY (
    SELECT '  [' + column_name + '] ' + data_type + CASE data_type
            WHEN 'sql_variant'
                THEN ''
            WHEN 'text'
                THEN ''
            WHEN 'ntext'
                THEN ''
            WHEN 'xml'
                THEN ''
            WHEN 'decimal'
                THEN '(' + cast(numeric_precision AS VARCHAR) + ', ' + cast(numeric_scale AS VARCHAR) + ')'
            ELSE coalesce('(' + CASE 
                        WHEN character_maximum_length = - 1
                            THEN 'MAX'
                        ELSE cast(character_maximum_length AS VARCHAR)
                        END + ')', '')
            END + ' ' + CASE 
            WHEN EXISTS (
                    SELECT id
                    FROM syscolumns
                    WHERE object_name(id) = so.NAME
                        AND NAME = column_name
                        AND columnproperty(id, NAME, 'IsIdentity') = 1
                    )
                THEN 'IDENTITY(' + cast(ident_seed(so.NAME) AS VARCHAR) + ',' + cast(ident_incr(so.NAME) AS VARCHAR) + ')'
            ELSE ''
            END + ' ' + (
            CASE 
                WHEN IS_NULLABLE = 'No'
                    THEN 'NOT '
                ELSE ''
                END
            ) + 'NULL ' + CASE 
            WHEN information_schema.columns.COLUMN_DEFAULT IS NOT NULL
                THEN 'DEFAULT ' + information_schema.columns.COLUMN_DEFAULT
            ELSE ''
            END + ', '
    FROM information_schema.columns
    WHERE table_name = so.NAME
    ORDER BY ordinal_position
    FOR XML PATH('')
    ) o(list)
LEFT JOIN information_schema.table_constraints tc ON tc.Table_name = so.NAME
    AND tc.Constraint_Type = 'PRIMARY KEY'
CROSS APPLY (
    SELECT '[' + Column_Name + '], '
    FROM information_schema.key_column_usage kcu
    WHERE kcu.Constraint_Name = tc.Constraint_Name
    ORDER BY ORDINAL_POSITION
    FOR XML PATH('')
    ) j(list)
WHERE xtype = 'U'
    AND NAME = @tblname

You can parse your results as you want.

score 0 · Answer 4 · answered Sep 20 '21 at 15:18

SELECT 
        COLUMN_NAME, 
        CASE 
        WHEN DATA_TYPE ='decimal' THEN DATA_TYPE+ '('+cast (NUMERIC_PRECISION as Varchar)+ ','+cast (NUMERIC_SCALE as Varchar)+ ')'
        WHEN CHARACTER_MAXIMUM_LENGTH is null THEN DATA_TYPE + cast ('' as Varchar) 
        ELSE DATA_TYPE + '('+ cast (CHARACTER_MAXIMUM_LENGTH as Varchar)+')' END AS QuantityText
    
    FROM 
        INFORMATION_SCHEMA.COLUMNS
    WHERE 
        TABLE_NAME = 'table_name' and TABLE_SCHEMA = 'dbo'
        order by COLUMN_NAME

SQL get table columns with type name

4 Answers4