1

During my database design process I have begun to add Table Types to the database and have run into the need to alter a table type. It is painfully clear that one cannot remove or drop the table type if it is being used in stored procedure(s); and unless one drops the related sprocs first, the table type can't be dropped/altered.

I get why the table type is important in an active database and why it should not be dropped if live, but if one is in a development mode is there/why not be able to turn off the database, so to drop the table type, and the add the modified type and restart? ...

đź’ˇ So is there a way to stop the checking and allow the drop of the table type, hence avoid the warnings to be able to alter the type?

2

No, there is no way to alter a table type, or drop it while any object references it directly. It would be nice to have a "just trust me, I know what I'm doing" mode, but we don't; you'll have to file a feature request to get that functionality.

In the meantime, what I would suggest is to just create a new table type, and then update the stored procedures in the order that they need to use the new type's definition. This prevents breaking the old procedures until you can modify them, too. A little more info here:

| improve this answer | |
  • I will file one as such thanks. But what I do is script out the sprocs, drop them, then change the table type and then reconstitute the sprocs. – ΩmegaMan Jun 23 at 14:40
  • Sure, there are multiple ways to do it, and it depends on exactly what you are trying to accomplish. Temporarily having two types allows you to work on changes to individual procedures instead of having to change all of the procedures at once. In a dev environment, this can be beneficial, IMHO. – Aaron Bertrand Jun 23 at 15:40
1

You can also use a tool like SSDT (SQL Server Data Tool).
If you import a database schema into a project you can just change file which represents the type definition. After such change SSDT will automatically generate publish script which will:

  1. Drop all dependent stored procedures.
  2. Drop old type.
  3. Create new type.
  4. Recreate all procedures.
| improve this answer | |
  • I do something similar, I, by hand, script out the sprocs, then drop them, the update the table type, and finally run the sproc scripts reconstituting what was there. What you describe, is it targeted to the table type and the process knows what to do, or do I have to guide it? – ΩmegaMan Jun 23 at 14:38
  • The tool will know what to do, you can use it for any database model changes. – Piotr Jun 23 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.