Using User-defined Types Across Databases
When you create a user-defined type, it is local to a single database. Therefore, a user-defined type defined in one database cannot be used in a column definition of another database. However, if the same user-defined type is registered and can be accessed in two databases, you can convert a user-defined type value from one database for use in another database. This ability is useful when you want to do the following:
- Call a stored procedure that is defined in a different database.
- Query over tables that are defined in two different databases.
- Select data from a database and insert it into another.
For example, suppose that user-defined type u1 is created in both database db1 and database db2. Stored procedure p2 is created only in database db2 and takes a type u1 parameter. You can call proc p2 with an instance of type u1 from database db1.
USE db1
DECLARE @u u1
EXEC db2.dbo.p2 @u
GO
Another example involves populating a temporary table in tempdb with the results of a query. The user-defined type must be registered in tempdb for this operation to succeed.
INSERT INTO #t SELECT * FROM T
Registering a user-defined type in tempdb is not required when you use a user-defined type in a table variable, or when a cursor uses the user-defined type in a worktable.
When you use user-defined types across databases, you should remember the following:
- The types must have the same name, including the same CLR name, and must be implemented through the same assembly in both databases. Assemblies in two databases are identical if they have the same name, strong name, culture, version, code access permission set, and binary contents.
- You must have SELECT and EXECUTE permissions on each user-defined type column referenced in the query. These permissions are checked with regard to the database in which each column is defined.
- If a type that is implemented in one assembly calls a method in another assembly, the cross-assembly call is checked in the same way as if the user-defined type query were issued without crossing databases.
See Also
Concepts
Working with CLR User-defined Types