Get Table Or View Column With Type - SQL Server

Posted by: bart30508 | in SQL Server | 2 months, 1 week 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
 or 

Subscribe

* indicates required

Recent Posts

Archive

2022
2021

Categories

Apache 1

Data Science 2

Dbfit 1

Design Pattern 1

Devops 3

DigitalOcean 1

Django 1

English 3

Excel 5

Flask 3

Git 1

HackMD 1

Heroku 1

Html/Css 1

Linux 4

Machine Learning 2

Manufacture 1

Mezzanine 18

Oracle 1

Postgresql 7

PowerBI 4

Powershell 4

Python 21

SEO 2

SQL Server 51

SQLite 1

Windows 1

database 8

work-experience 1

其他 1

自我成長 1

資料工程 1

Tags

SEO(1) Github(2) Title Tag(2) ML(1) 李宏毅(1) SQL Server(18) Tempdb(1) SSMS(1) Windows(1) 自我成長(2) Excel(1) python Flask(1) python(5) Flask(2)

Authors

bart30508 (146)

Feeds

RSS / Atom

Get Table Or View Column With Type - SQL Server

© COPYRIGHT 2011-2022. Max的文藝復興. ALL RIGHT RESERVED.