SQLTeam.com | Weblogs | Forums

Add primary key to User-Defined Table Type already in use


How can I add a Primary Key to user defined table after it's been created and in use? I can't script as ALTER, and I can't drop and re-create because it's being referenced by a bunch of procedures.

We have a user defined table type that has a single int column that we use to as a result set filter against some big tables. Whoever created the key didn't add a primary key, it's just a TABLE ([ID] [int] NOT NULL). If I use this in a new query, it's doing a full table scan of the large tables and eliminating any benefit of the table join. Is there any way I can add a key after the fact?




The error message I get when trying to do that is:
"Cannot find the object "[dbo].[MyTable]" because it does not exist or you do not have permissions."
User defined table creation:

[ID] [int] NOT NULL

Declaring and using table:
DECLARE @ResultSet IDTableType
ALTER TABLE [@ResultSet]



I do not think you can ALTER a TYPE.

If the tables being passed to your SPs have a primary key I am suprised that it is not being used.

If you do need to alter the type definition I suspect you are just going to have to script out the SPs and then DROP them, DROP the type, CREATE the type and then CREATE the SPs.


(Oh, boy!)..... You need to substitute the generic "MyTable" with the name of your actual table (@ResultSet). You'll also need to substitute the generic "MyColumn" with your actual column or columns (ID). You don't need to but probably should change the name of the primary key from "PK_MyTable" to something that makes sense in your environment.


I changed the error message after I pasted it in to match your example, the actual one was: "Cannot find the object "@ResultSet" because it does not exist or you do not have permissions." I didn't just paste in your MyTable example and run it.

If you run the code I provided as an example to add a new type named IDTableType, and then attempt to alter the table after declaration, you'll see that you get the error message with @ResultSet in there, or whatever you've decided to name it.

I tried with brackets and without, with schema and without, you either get an incorrect syntax, or the cannot find the object error.


Try to remove the square brackets around the table name. They cause the SQL engine to bypass some of the validation. The end result, I believe, is that it is looking for a permanent table named "@ResultSet" and not a table variable with that name.