SQL SERVER查詢表結構資訊–方便匯出

--column_name列名

--column_data_type列的資料類型

--max_length長度

--precision有效位元 如decimal類似

--scale比例

--is_nullable是否允許為空白

--emp　為所查詢的表名

 

select C.name AS column_name,P.[name] AS column_data_type,C.max_length,P.[precision],P.scale,P.is_nullable
from sys.columns C
inner join sys.tables T on C.[object_id]=T.[object_id]
left join sys.types P on C.[user_type_id]=P.[user_type_id]
left join sys.computed_columns a on c.[object_id]=a.[object_id] and c.[column_id]=a.[column_id]
left join sys.default_constraints b on c.[object_id]=b.[parent_object_id] and c.[default_object_id]=b.[object_id]
where T.[name]='EMP'