Way to use $OBJECT(..)$ macro inside $PROMPT(..)?

I'm trying to make a snippet that prompts for a table and then an index name. I'd like the index name to prefix with IX_TableName

	Code:
	$PROMPT(idx_name,What is the name of your index?,IX_$OBJECT(ins_object,table)$,INDEX NAME)$ CREATE INDEX $idx_name$ ON $OBJECT(ins_object, table)$(\|) WITH(FILLFACTOR=80, SORT_IN_TEMPDB=OFF) GO

This is what I'm using for the prompt, but the default value takes most of it as a string literal but cuts off at the comma (presumably because it's parsing for parameters) and my prompt title becomes 'table' instead of 'INDEX NAME'.

Is there a way to make this work? I tried escaping the $'s with ^ (caret) but was not able to succeed.

Last edited by Mindflux on Sun May 07, 2023 10:52 pm; edited 1 time in total

Well, well I got it with a $$..$$ macro instead.. though the positional (pipe) is being used as a literal.

*Edit: got this to work with more $$ .. $$ macros ... it aint pretty.

	Code:
	$$ SELECT 'CREATE INDEX IX_$OBJECT(ins_object,table)$_'$$\|$$ SELECT ' ON $OBJECT(ins_object,table)$( $COLUMNS(vertical,nokeys)$ ) WITH(FILLFACTOR=80,SORT_IN_TEMPDB=OFF)' $$

I had asked this a few years back and I recall something about it not being possible due to the order of the evaluation of the macros ($$..$$ goes last, I guess). But I hope SysOp can elaborate.

That is still the case. An alternative for $PROMPT...$ is to use a list for the prompt instead of an edit box and populate that list with table names, though I personally don't think that will work well practically.

Another alternative is to mouse over table name, if it's already in the code (or Database Explorer if you use one), then click the Table name hyperlink in the balloon and click Indexes tab. that takes a few seconds only. You can then choose columns and options in a graphical dialog. You can also review definitions of the existing indexes right there which may help you with choosing columns for new indexes, as well as modify and drop existing indexes if you want to optimize them. You don't want to build or update indexes immediately, script to clipboard or script to editor are available, as well as scheduling.

Now that's hilarious. It only took this many years for me to notice that I can click the table name in the popup. And even then not by accident...

	gemisigo wrote:
	Now that's hilarious. It only took this many years for me to notice that I can click the table name in the popup. And even then not by accident...

Every now and then when I have to dig out the SQL Assistant help PDF I always learn something new.

Last night I learned ctrl+alt+h will highlight the the nearest statement relative the cursor position. (left or upward, anyway)

	gemisigo wrote:
	Now that's hilarious. It only took this many years for me to notice that I can click the table name in the popup. And even then not by accident...

Have you tried recently the Info link in the same balloon, or in right-click menus? The Info pane offers table reports including reports for index space usage and fragmentation. Though reports vary by database type.

	SysOp wrote:
	Have you tried recently the Info link in the same balloon, or in right-click menus? The Info pane offers table reports including reports for index space usage and fragmentation. Though reports vary by database type.

I've been tinkering with the DB Query powering that popup for a very long time now (you can see the recently updated version of it here), but I never noticed those links to the reports on the right side of that pane. Another fine example of literal (or rather lateral?) "tunnel vision" :) Since when are those there?

Access to the Properties dialogs via name hyperlink was introduced in 12.0, the report links in the Info panel were introduced in 12.2, not that long ago. They are also accessible via menus in the Database Explorer, with a few more clicks. You can add custom reports as well, if you save them to C:\Program Files (x86)\SQL Assistant 12\reports\[db type]\Object Info\Table folder. Their names will be added to the Info pane and to right-click menus.