列出某資料表內所有欄位的sql查詢語法2 @ 用鵝毛筆沾咖啡畫ER Model

use [AgriUnited];

declare @tableName nvarchar(50) = 'AgriProdOrder';

-- 取資料表的 PK
SELECT KU.table_name as TABLENAME,column_name as PRIMARYKEYCOLUMN
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND
KU.table_name=@tableName
ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION;

-- 取資料表的所有欄位資訊
select
--st.name [Table],
sc.name [欄位名稱],
       --tp.name [DataType],
       --info.character_maximum_length,
       case
           when info.character_maximum_length is null then tp.name
           else tp.name+ '('+cast(info.character_maximum_length as nvarchar)+')'
           end [型態],
       case
           when info.COLUMN_DEFAULT is null then ''
           else info.COLUMN_DEFAULT
           end [預設值],
       case
           when sc.is_nullable = 1 then ''
           else 'NOT NULL'
           end [是否NULL],
       case
           when sep.value is null then ''
           else sep.value
           end [說明]
from sys.tables st
inner join sys.columns sc on st.object_id = sc.object_id
left join sys.extended_properties sep on st.object_id = sep.major_id
and sc.column_id = sep.minor_id
and sep.name = 'MS_Description'
   JOIN sys.types tp ON tp.user_type_id = sc.user_type_id
   join (SELECT *
       FROM INFORMATION_SCHEMA.COLUMNS
       WHERE TABLE_NAME = @tableName) info
on info.COLUMN_NAME = sc.name
where st.name = @tableName
--and sc.name = @ColumnName