SQLServer 找表格所有欄位 欄位型態 欄位備註 | SQLServer query table column type and comment

markdown

```
SELECT c.name '欄位名稱', ep.value AS '欄位備註', t.Name '欄位型態', c.max_length '最大byte', c.precision '總位數', c.scale '小數位數', c.is_nullable '是否可為空', ISNULL(i.is_primary_key, 0) '是否是PK' FROM sys.columns c INNER JOIN sys.types t ON c.user_type_id = t.user_type_id INNER JOIN sys.objects ON sys.objects.object_id = c.object_id LEFT OUTER JOIN sys.index_columns ic ON ic.object_id = c.object_id AND ic.column_id = c.column_id LEFT OUTER JOIN sys.indexes i ON ic.object_id = i.object_id AND ic.index_id = i.index_id OUTER APPLY fn_listextendedproperty(default, 'SCHEMA', schema_name(sys.objects.schema_id), 'TABLE', sys.objects.name, 'COLUMN', c.name) ep WHERE c.object_id = OBJECT_ID('your table name') ```

ref:
https://stackoverflow.com/questions/2418527/sql-server-query-to-get-the-list-of-columns-in-a-table-along-with-data-types-no
https://devio.wordpress.com/2009/08/19/retrieving-table-and-column-descriptions-in-sql-server/