Get Table Or View Column With Type - SQL Server

Posted by: Max Chen | in SQL Server | 1 year, 2 months ago |

Script like below:

-- get table or view column
DECLARE @TableViewName NVARCHAR(128)

SET @TableViewName = N'testinvoice' -- table name

SELECT b.name AS ColumnName
    ,c.name AS DataType
    ,b.max_length AS Length
    ,b.precision as DataTypeprecision
    ,b.scale as DataTypescale
    ,c.Precision AS SystemMaxPrecision
    ,c.Scale AS SystemMaxScale
    ,d.value AS Description
FROM sys.all_objects a
INNER JOIN sys.all_columns b ON a.object_id = b.object_id
INNER JOIN sys.types c ON b.user_type_id = c.user_type_id
LEFT JOIN sys.extended_properties d ON a.object_id = d.major_id
    AND b.column_id = d.minor_id
    AND d.name = 'MS_Description'
WHERE a.Name = @TableViewName
    AND a.type IN (
        'U'
        ,'V'
        )

Currently unrated