HOW TO: Change the Owner of a User-Defined Data Type That Is in Use in SQL Server 2000

喜欢ヅ旅行 2021-11-26 15:52 141阅读 0赞

This article describes how to change the owner of a user-defined data type that is in use.  
  
To change the owner of a user-defined data type, you must drop and then re-create the data type in the context of the owner you want. However, if the user-defined data type is in use, you will receive the following error message when you try to drop the data type:

Server: Msg 15180, Level 16, State 1, Procedure sp\_droptype, Line 32 Cannot drop. The data type is being used.

### Steps to Change the Owner of a User-Defined Data Type That Is in Use ###

To change the owner of a user-defined data type that is in use, follow these steps:

1.  Script out the definition of the user-defined data type with SQL Server Enterprise Manager (SEM).
2.  Expand your SQL Server, expand **Databases**, expand your database, and then expand **User defined data types**.
3.  In the right pane, right-click the data type you want, and then click **All Tasks**.
4.  Click **Generate SQL Script**, and then click **OK**.
5.  Select the file name and location in which you will store the script.
6.  Determine all the tables or columns that are using the user-defined data type by using the following code (replace the database name and data type with your database name and data type):
    
        USE database name
        SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME, DOMAIN_NAME
        FROM INFORMATION_SCHEMA.COLUMNS
        WHERE DOMAIN_NAME = 'data type'
    
    **NOTE**: In this context, DOMAIN\_NAME represents the user-defined data type.  
      
    
7.  For each table that is using the user-defined data type, change the column data type to the base data type by using an ALTER TABLE statement. For example to change **mytable..mycolumn** to the **datetime**base data type, use:
    
        ALTER TABLE mytable ALTER COLUMN mycolumn datetime
8.  Drop the user-defined data type.
9.  Re-create the user-defined data type by using the script you saved in step 4 under the context of the owner you want.
10. Change all the columns you want back to the user-defined data type by using an ALTER TABLE statement as in step 7.

转载于:https://www.cnblogs.com/MaxWoods/archive/2011/08/31/2160722.html