missing candidates when adding index or PK constraint

JDHnz

When writing a script containing "ALTER TABLE zzz ADD CONSTRAINT PRIMARY KEY NONCLUSTERED (" I would have expected to see a list of column candidates after I typed the opening parenthesis, but I didn't see anything until I typed the first letter of the column name and even then it was only a list of matching snippets.

Similarly, when adding an index, I expected but didn't get an appropriate list of columns at the relevant point in the CREATE INDEX statement.

Brian Donahue

I'm not sure what's going on there, but I suspect maybe the schema needs to be updated. You can do this through cache management. Hopefully after refreshing the database in question, you'll get the candidate list. It sounds to me as if prompt is working, but knows nothing about the schema or has an out-of-date version that is missing the table in question.

JDHnz

Can't say I agree with your analysis. In both of the cases, (1) where it is a new table also defined in the script, or (2) an existing table in the database, then the tables appear in the candidate lists where appropriate (with the new table identified using a "SQL" icon), but not for column lists. Refreshing the cache didn't help. The list of column candidates appears when expected for an INSERT or SELECT statement, but not for the ALTER TABLE or CREATE INDEX statements.

Ben Pinchen

I've given this a try and I experience the same issue as you. When adding constraints and indexes, SQL Prompt doesn't supply you with the correct column options e.g.

ALTER TABLE table1 ADD PRIMARY KEY NONCLUSTERED<prompt>

won't give the available columns from table1. This is something that we can definitely improve upon. Therefore I have raised it in our bug tracking software for the developers to look at. They are currently working on the next version of SQL Prompt and hopefully this is something that they'll be able to implement in this next version.