About modifying the "User-defined table type" issue in SQL Server

The source of this article: http://www.cnblogs.com/wy123/p/7282682.html
(It is not the original works right to retain the source, I my book still far to reach, just to link to the original text, because the following may exist some errors to amend or supplement, without him)

During SQL Server development, in order to pass variables of the type of the dataset (such as accepting DataTable type variables in C #), you need to define a "user-defined table type", which can receive a two-dimensional dataset as a parameter through the user-defined table type.
When you need to modify the user-defined table type, add the field, delete the field, modify the field type, and so on, and it does not modify the ALTER TABLE syntax like the tables.
This can only be done by deleting the rebuild, but when you delete the user-defined table type, you are prompted to refer to it (some stored procedures use this "user-defined table type") and cannot be deleted.
In order to achieve a common purpose, sometimes a tabletype can be referenced in several places, so that it is necessary to first delete all references to this "user-defined table type" object (stored procedures, etc.)
If this "user-defined table type" is referenced by multiple stored procedures, it is necessary to remove multiple stored procedures that refer to the user-defined table type, and then modify the "User-defined table type" to recreate the stored procedure, which seems to be a bit of a detour,
This problem can be used by the Exec sys.sp_refreshsqlmodule system function to introduce the definition of "user-defined table type"

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 using the user-defined table type defined above to simulate the case where the user-defined table type is referenced

Stored procedures at this time can receive the Tabletype parameter and run correctly

Modification of Tabletype

The Tabletype type does not support alter syntax, which means that the definition of Tabletype cannot be modified directly

Then only by removing the Tabletype method to reconstruct the Tabletype, when deleted, still error, "because it is being referenced by the object ' * * *". There may be other objects referencing this type. ”

At this point can only remove the reference to this Tabletype object to solve, the following can be found that the object refers to a certain tabletype, and then delete, rebuild Tabletype, and then rebuild the stored procedure, a bit mealy.

You can rename a custom Tabletype first, there is a warning in the process of renaming, it is ignored first, and then you can drop the Type dbo directly. Mytabletype

After deleting the original tabletype, rebuild (redefine) Tabletype

After rebuilding the tabletype, the stored procedure used in the previous stored procedure was not compiled by the Tabletype.

You will need to refresh the definition of the referenced object at this point

Once the refresh is complete, the original stored procedure can be compiled correctly.

Finally delete the original Tabletype renamed Tabletype (the one with the same name as the first step)

This way, the whole process does not need to modify the definition of Tabletype to remove the object that refers to Tabletype, after modifying the definition of Tabletype, reference to this Tabletype object can run normally, can also be used according to the modified Tabletype to do the specific use

The complete script is as follows

--determine if the type exists, if it exists, rename it, and then delete it later, otherwise it cannot be deleted directlyIF EXISTS(SELECT 1  fromSys.types TJoinSys.schemas s ont.schema_id=s.schema_id andT.name='Mytabletype'  andS.name='dbo')   EXECSys.sp_rename'dbo. Mytabletype','Obsoleting_mytabletype';GO--rebuild type, for example four fields, now want to change to three fields, or three fields originally want to add a field into four fieldsCREATETYPE dbo. Mytabletype as TABLE(IdINT  not NULL, NameVARCHAR(255) not NULL,
RemarkVARCHAR (255)

)GO--rebuild the original reference to be deleted, or the original stored procedure will errorDECLARE @Name NVARCHAR( -);DECLARERef_cursorCURSOR  forSELECTReferencing_schema_name+ '.' +Referencing_entity_name fromSys.dm_sql_referencing_entities ('dbo. Mytabletype','TYPE'); OPENRef_cursor; FETCH NEXT  fromRef_cursor into @Name;  while(@ @FETCH_STATUS = 0)    BEGIN        EXECSys.sp_refreshsqlmodule@name = @Name; FETCH NEXT  fromRef_cursor into @Name; END;CLOSERef_cursor;deallocateRef_cursor;GO--finally delete the original renamed Tabletype (the one with the same name as the first step)IF EXISTS(SELECT 1  fromsys.types TJoinSys.schemas s ont.schema_id=s.schema_id andT.name='Obsoleting_mytabletype'  andS.name='dbo')   DROPTYPE Dbo.obsoleting_mytabletypeGO--Final Executive AuthorizationGRANT EXECUTE  onTYPE::d bo. Mytabletype to  PublicGO

Summarize:

Tabletype can easily accept the two-dimensional data as parameters, so as to achieve the purpose of batch processing data, avoid passing in a lot of strings, and then in the practice of parsing strings, so as to a certain extent, improve the efficiency of SQL operation.
However, there are certain problems in the modification of tabletype, direct modification of TABLETYPE will exist cascade Delete database objects, can be "curve salvation" way, to reduce the workload of the situation to modify Tabletype.

About modifying the "User-defined table type" issue in SQL Server