HOW TO: Change the Owner of a User-Defined Data Type That Is in Use in SQL Server 2000 喜欢ヅ旅行 2021-11-26 15:52 106阅读 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
还没有评论,来说两句吧...