[SQL]找出那些Table有使用到User-Defined Data Types
我們可以從 sys.types 這個View的is_user_defined欄位值為1，就是使用者定義型別，如下，
SELECT *
FROM sys.types AS t
WHERE t.is_user_defined = 1;
然後再跟 sys.columns 這個View，透過user_type_id欄位 Join ，就可以找出資料庫中有用到User-Defined Data Types的物件，可透過 OBJECTPROPERTY 來判斷是Table、View還是Function等等，如下，
SELECT OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName, t.name
, OBJECTPROPERTY(c.object_id, 'IsTable') IsTable
, OBJECTPROPERTY(c.object_id, 'IsView') IsView
, OBJECTPROPERTY(c.object_id, 'IsTableFunction') IsTableFunction
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.is_user_defined = 1
ORDER BY c.OBJECT_ID;
所以，如果只要找出那些Table的話，就加入篩選條件 OBJECTPROPERTY(c.object_id, 'IsTable') = 1 ，就可以了，如下，
SELECT OBJECT_NAME(c.OBJECT_ID) TableName, c.name ColumnName, t.name
FROM sys.columns AS c
JOIN sys.types AS t ON c.user_type_id=t.user_type_id
WHERE t.is_user_defined = 1
AND OBJECTPROPERTY(c.object_id, 'IsTable') = 1
ORDER BY c.OBJECT_ID;
Hi,
亂馬客Blog已移到了 「亂馬客 : Re:從零開始的軟體開發生活」
請大家繼續支持 ^_^
