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

1

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?

2

ALTER TABLE [dbo].[MyTable] ADD CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED ([MyColumn] ASC)

3

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:

CREATE TYPE [dbo].[IDTableType] AS TABLE(
[ID] [int] NOT NULL
)

Declaring and using table:
DECLARE @ResultSet IDTableType
ALTER TABLE [@ResultSet]
ADD CONSTRAINT [PK_ResultSet] PRIMARY KEY CLUSTERED ([ID] ASC)

INSERT INTO @ResultSet (ID)
VALUES(123456);

4

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.

5

(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.

6

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.

7

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.