[SQL]使用者自訂類型(User-defined Data Types)

2012-08-16
7850
0
SQL
上課有上到，紀錄一下步驟及相關SQL
新增使用者自訂類型步驟
1. 可程式性→類別→新增→使用者定義資料類型
2. 設定相關型態，這裡以員工編號(STAFF、nvarchar(6))為例
3. 建立完畢可以看到相關定義
4. 要看那些欄位為使用者定義資料類型，可以看「檢視相依性」
相關的SQL

-- 資料來源:德瑞克老師的上課筆記+自行修改
-- sys.types (Transact-SQL)
-- http://technet.microsoft.com/zh-tw/library/ms188021.aspx
-- sys.columns
-- http://msdn.microsoft.com/zh-tw/library/ms176106.aspx
-- 建立使用者定義資料類型別名
-- http://msdn.microsoft.com/zh-tw/library/ms190232.aspx
USE tempdb
GO
-- 建立類型
IF EXISTS
(SELECT name
 FROM
	 sys.types
 WHERE
	 name = 'STAFF') DROP TYPE dbo.STAFF
GO
CREATE TYPE STAFF
FROM nvarchar(6) NOT NULL;
GO

-- 建立測試資料表格
IF  EXISTS
(SELECT *
 FROM
	 sys.objects
 WHERE
	 object_id = object_id(N'[dbo].[EMPLOYEE_USERDATATYPE]')
	 AND type IN (N'U')) DROP TABLE [dbo].[EMPLOYEE_USERDATATYPE]
GO
CREATE TABLE EMPLOYEE_USERDATATYPE
(EMPID STAFF , cData nvarchar(50))
GO

-- 尋找表格中那些欄位定類型為使用者自訂類型
DECLARE @tablename sysname
SET @tablename = N'EMPLOYEE_USERDATATYPE';

SELECT @tablename N'表格名稱'
	 , c.name N'資料行名稱'
	 , t.name N'使用者資料類型'
	 , c.max_length N'資料行的最大長度(Byte)'
	 , d.name N'基本資料型態'
	 , (CASE
		   WHEN d.name = 'nvarchar' AND c.max_length > 0 THEN
			   c.max_length / 2
		   WHEN d.name = 'nchar' AND c.max_length > 0 THEN
			   c.max_length / 2
		   WHEN d.name = 'ntext' AND c.max_length > 0 THEN
			   c.max_length / 2
		   ELSE
			   c.max_length
	   END) N'欄位長度'
FROM
	sys.columns c
	INNER JOIN sys.types t
		ON c.user_type_id = t.user_type_id
	INNER JOIN sys.types d
		ON d.system_type_id = c.system_type_id AND d.system_type_id = d.user_type_id
WHERE
	[object_id] = object_id(@tablename)
	AND t.is_user_defined = 1
GO

-- 查詢那些資料行資料類型是使用者定義資料類型

DECLARE @typename sysname
SET @typename = N'STAFF';
CREATE TABLE #columndef 
(
	[表格名稱] nvarchar(200),
	[資料行名稱] sysname,
	[使用者資料類型] sysname,
	[資料行的最大長度(Byte)] int,
	[基本資料類型] sysname,
	[欄位長度] int	
);

EXEC sp_MSforeachtable ' INSERT INTO #columndef
SELECT ''?'' N''表格名稱'',c.name N''資料行名稱'',
	t.name N''使用者資料類型'',
	c.max_length N''資料行的最大長度(Byte)'',
	d.name N''基本資料類型'',
	(CASE
			WHEN d.name = ''nvarchar'' AND c.max_length > 0 THEN
				c.max_length / 2
			WHEN d.name = ''nchar'' AND c.max_length > 0 THEN
				c.max_length / 2
			WHEN d.name = ''ntext'' AND c.max_length > 0 THEN
				c.max_length / 2
			ELSE
				c.max_length
		END) N''欄位長度''
	
FROM sys.columns c INNER JOIN sys.types t
	ON c.user_type_id = t.user_type_id
	INNER JOIN sys.types d
	ON d.system_type_id= c.system_type_id AND d.system_type_id = d.user_type_id
WHERE [object_id] = object_id(''?'')
and t.is_user_defined = 1';

SELECT *
	 , 'ALTER TABLE ' + [表格名稱] + ' ALTER COLUMN ' + [資料行名稱] + ' ' + [基本資料類型] + 
	 '(' + cast([欄位長度] AS VARCHAR) + ')' N'還原基本型態指令'
	 , 'ALTER TABLE ' + [表格名稱] + ' ALTER COLUMN ' + [資料行名稱] + ' ' + [使用者資料類型] 
	 N'設定使用者資料類型指令'
FROM
	#columndef
WHERE
	[使用者資料類型] = @typename

DROP TABLE #columndef
GO

-- 還原基本型態指令
-- ALTER TABLE [dbo].[EMPLOYEE_USERDATATYPE] ALTER COLUMN EMPID nvarchar(6)
-- 設定使用者資料類型指令
-- ALTER TABLE [dbo].[EMPLOYEE_USERDATATYPE] ALTER COLUMN EMPID STAFF

-- 修改使用者自訂欄位定義
-- 1.修改「資料行(Column)」，改用內建的資料類型
 ALTER TABLE [dbo].[EMPLOYEE_USERDATATYPE] ALTER COLUMN EMPID nvarchar(6)
GO
-- 2.刪除沒有使用的「使用者自訂類型(User-defined Data Types)」
DROP TYPE dbo.STAFF
GO
-- 3.再度建立「使用者自訂類型(User-defined Data Types)」
 CREATE TYPE STAFF
 FROM nvarchar(15) NOT NULL;
GO
-- 4.修改「資料行(Column)」，改用「使用者自訂類型(User-defined Data Types)」
 ALTER TABLE [dbo].[EMPLOYEE_USERDATATYPE] ALTER COLUMN EMPID STAFF
GO

-- 查看變更後的結果
DECLARE @tablename sysname
SET @tablename = N'EMPLOYEE_USERDATATYPE';

SELECT @tablename N'表格名稱'
	 , c.name N'資料行名稱'
	 , t.name N'使用者資料類型'
	 , c.max_length N'資料行的最大長度(Byte)'
	 , d.name N'基本資料型態'
	 , (CASE
		   WHEN d.name = 'nvarchar' AND c.max_length > 0 THEN
			   c.max_length / 2
		   WHEN d.name = 'nchar' AND c.max_length > 0 THEN
			   c.max_length / 2
		   WHEN d.name = 'ntext' AND c.max_length > 0 THEN
			   c.max_length / 2
		   ELSE
			   c.max_length
	   END) N'欄位長度'
FROM
	sys.columns c
	INNER JOIN sys.types t
		ON c.user_type_id = t.user_type_id
	INNER JOIN sys.types d
		ON d.system_type_id = c.system_type_id AND d.system_type_id = d.user_type_id
WHERE
	[object_id] = object_id(@tablename)
	AND t.is_user_defined = 1
GO

-- 還原資料
ALTER TABLE [dbo].[EMPLOYEE_USERDATATYPE] ALTER COLUMN EMPID nvarchar(15)
GO
IF EXISTS
(SELECT name
 FROM
	 sys.types
 WHERE
	 name = 'STAFF') DROP TYPE dbo.STAFF
GO
IF  EXISTS
(SELECT *
 FROM
	 sys.objects
 WHERE
	 object_id = object_id(N'[dbo].[EMPLOYEE_USERDATATYPE]')
	 AND type IN (N'U')) DROP TABLE [dbo].[EMPLOYEE_USERDATATYPE]
GO
回首頁