SQL Server/T-SQL Tutorial/Data Types/Create Type — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 13:25, 26 мая 2010
Содержание
Creating and Using User-Defined Types
<source lang="sql">
4> 5> CREATE TYPE dbo.AccountNBR FROM char(14) NOT NULL 6> GO 1> 2> CREATE TABLE dbo.MyCount 3> (ID int NOT NULL, 4> AccountNBR AccountNBR) 5> GO 1> 2> CREATE PROCEDURE dbo.usp_SEL_CustomerAccount @CustomerAccountNBR AccountNBR 3> AS 4> SELECT ID, AccountNBR 5> FROM dbo.MyCount 6> WHERE AccountNBR = @CustomerAccountNBR 7> GO 1> 2> DECLARE @AccountNBR AccountNBR 3> SET @AccountNBR = "1294839482" 4> EXEC dbo.usp_SEL_CustomerAccount @AccountNBR 5> GO ID AccountNBR
--------------
1> 2> EXEC sp_help "dbo.AccountNBR" 3> GO Type_name
Storage_type Length Prec Scale Nullable Default_name Rule_name Collation
---------------------------------------------------------------------------------------------------------------
------ ----------- ----------- ----------------------------------- -----------------------------------
--------------------------
-----------------
AccountNBR
char 14 14 NULL no none none Chinese_PRC_CI_AS
1> 2> drop table dbo.MyCount 3> GO 1> drop procedure dbo.usp_SEL_CustomerAccount 2> GO 1> drop type dbo.AccountNBR 2> GO 1></source>
Dropping User-Defined Types
<source lang="sql">
4> 5> CREATE TYPE dbo.AccountNBR FROM char(14) NOT NULL 6> GO 1> 2> DROP TYPE dbo.AccountNBR 3> GO 1></source>
Identifying Columns and Parameters that Use User-Defined Types
<source lang="sql">
4> 5> CREATE TYPE dbo.AccountNBR FROM char(14) NOT NULL 6> GO 1> 2> CREATE TABLE dbo.MyCount 3> (ID int NOT NULL, 4> AccountNBR AccountNBR) 5> GO 1> 2> SELECT OBJECT_NAME(c.object_id) Table_Name, c.name Column_Name 3> FROM sys.columns c 4> INNER JOIN sys.types t ON 5> c.user_type_id = t.user_type_id 6> WHERE t.name = "AccountNBR" 7> GO Table_Name
Column_Name
---------------------------------------------------------------------------------------------------------------
MyCount
AccountNBR
1> 2> 3> drop table dbo.MyCount 4> GO 1> 2> drop type dbo.AccountNBR 3> GO</source>
Now see what parameters reference the AccountNBR data type
<source lang="sql">
3> 4> CREATE TYPE dbo.AccountNBR FROM char(14) NOT NULL 5> GO 1> 2> 3> SELECT OBJECT_NAME(p.object_id) Table_Name, p.name Parameter_Name 4> FROM sys.parameters p 5> INNER JOIN sys.types t ON 6> p.user_type_id = t.user_type_id 7> WHERE t.name = "AccountNBR" 8> GO Table_Name
Parameter_Name
---------------------------------------------------------------------------------------------------------------
1> 2> drop type dbo.AccountNBR 3> GO 1></source>
The syntax of the CREATE TYPE statement is as follows:
<source lang="sql">
CREATE TYPE [type_schema_name.] type_name
{[FROM base_type [(precision [,scale])] [NULL|NOT NULL]] |[EXTERNAL NAME assembly_name [.class_name]]}
10> CREATE TYPE zip 11> FROM CHAR(5) NOT NULL 12> GO 1> 2> drop type zip; 3> GO</source>