Analysis and method for modifying the "user-defined table type" in SQL Server, SQL Server

Analysis and method for modifying the "user-defined table type" in SQL Server, SQL Server

Preface

During SQL Server development, to pass in a dataset type variable (for example, to accept a DataTable type variable in C #), you need to define a "user-defined table type ", you can use "user-defined table type" to receive two-dimensional datasets as parameters. When you need to modify the "user-defined table type", add, delete, and modify fields, it does not have the same alter table syntax as the table.

It can only be implemented by deleting and recreating, however, when you delete the "user-defined table type", an object will be prompted to reference it (this "user-defined table type" is used in some stored procedures). Therefore, it cannot be deleted.

For public purposes, sometimes a TableType can be referenced in multiple places. In this case, delete all objects that reference the "user-defined table type" (stored procedures, etc)

If the "user-defined table type" is referenced by multiple stored procedures, you must delete multiple stored procedures that reference the "user-defined table type, then modify the "user-defined table type" and re-create the stored procedure. This seems to be a bit difficult to do. You can use EXEC sys to solve this problem. sp_refreshsqlmodule: A system function that describes the implementation of the "user-defined table type" definition.

Basic use of TableType

Create a user-defined table type as follows

The defined TableType can be found in the User-Defined table type.

Create two stored procedures and use the User-Defined table type defined above to simulate the case where the user-defined table type is referenced.

In this case, the stored procedure can receive the TableType parameter and run normally.

TableType Modification

TableType does not support alter syntax, that is, you cannot directly modify the definition of TableType.

You can only use the TableType deletion method to recreate the TableType. When the TableType is deleted, an error is still reported, prompting "because it is being referenced by the object. Other objects may reference this type ."

In this case, you can only delete the object that references this TableType. You can find the objects that reference a certain TableType, delete them separately, recreate the TableType, and recreate the stored procedure.

You can first rename a custom TableType, and there is a warning during the rename process. Ignore it here, and then you can directly Drop Type dbo. MyTableType

After deleting the original TableType, recreate (redefine) TableType

After the TableType is rebuilt, the stored procedure that uses this TableType in the previous stored procedure cannot be compiled.

In this case, you need to refresh the definition of the referenced object.

After refreshing, the original stored procedure can be compiled normally.

Finally, delete the renamed TableType of the original TableType (the one with the same name as the first step)

In this way, the entire process does not need to delete the object that references TableType because of modifying the definition of TableType. After the definition of TableType is modified, the object that references this TableType can run normally, you can also use the modified TableType

The complete script is as follows:

-- Determine whether the Type EXISTS. IF yes, rename it and delete it later. Otherwise, if exists (SELECT 1 FROM sys. types t join sys. schemas s on t. schema_id = s. schema_id and t. name = 'mytabletype' and s. name = 'dbo') EXEC sys. sp_rename 'dbo. myTableType ', 'obsoleting _ MyTableType'; GO -- re-creates the TYPE. For example, if the original TYPE is four fields, change it to three fields, or three fields have been added to create type dbo. myTableType as table (Id int not null, Name VARCHAR (255) not null, Remark VARCHAR (255) GO -- re-create all the types to be deleted from the original reference, otherwise, the error DECLARE @ Name NVARCHAR (500); DECLARE REF_CURSOR cursor forselect referencing_schema_name + 'will be reported in the original stored procedure '. '+ referencing_entity_nameFROM sys. dm_ SQL _referencing_entities ('dbo. myTableType ', 'type'); OPEN REF_CURSOR; fetch next from REF_CURSOR INTO @ Name; WHILE (@ FETCH_STATUS = 0) begin exec sys. sp_refreshsqlmodule @ name = @ Name; fetch next from REF_CURSOR INTO @ Name; END; CLOSE REF_CURSOR; DEALLOCATE REF_CURSOR; GO -- delete the original renamed TableType (the one with the same name as the first step) if exists (SELECT 1 FROM sys. types t join sys. schemas s on t. schema_id = s. schema_id and t. name = 'obsoleting _ mytabletype' and s. name = 'dbo') drop type dbo. obsoleting_MyTableTypeGO -- finally EXECUTE the authorization grant execute on type: dbo. myTableType TO publicGO

Summary:

TableType can easily accept two-dimensional data as parameters, so as to achieve the goal of batch data processing, avoid passing in a lot of strings, and then in the string parsing practices, this improves the SQL running efficiency to a certain extent.

However, the modification of TableType does have some problems. Directly modifying TableType will cause cascading deletion of database objects. You can "save the country by curve, to reduce the workload.

Well, the above is all the content of this article. I hope the content of this article will help you in your study or work. If you have any questions, you can leave a message, thank you for your support.