Unable to pass scema.object to the query shortcut in SSMS 2016

Question

I am having a query shortcut that is generating create table in SQL Server Management Studio 2016. This works fine when I am not trying to select schema.table, but once I am trying to select such object I am getting error Incorrect syntax near '.'.

My query shortcut looks like:

exec sp_executesql 'huge sql oneliner', N'@table_name SYSNAME', @table_name =

Any idea how I can pass objects like that to the query shortcut?

update 1:

The only workaround I kind of found is to wrap the table name with the single quotes, which is a bit annoying.

update 2:

I simplified my shortcut a bit for debugging purposes:

exec sp_executesql N'select @table_name;', N'@table_name varchar(max)', @table_name =

and when I ran it this is what I captured in the Profiler:

exec sp_executesql N'select @table_name;', N'@table_name varchar(max)', @table_name = dbo.Categories

which makes sense for the workaround I found earlier, but still would love to make it work without surrounding it in single quotes by myself

Answer 1

The result of a shortcut must still be valid SQL. As the documentation makes clear, the parameter values must be surrounded by quotes. You could use trace or Profiler to capture the actual code submitted to see exactly what's going wrong.

As the additional text seems to be appended to the shortcut rather than embedded in it there's no way to pre-wrap the additional text in quotes.

Perhaps a custom snippet would suit your needs better? Though it does take a little work to establish and you're obviously sensitive to gratuitous keystrokes.

If you're feeling very brave you could change the user's default schema inside the dynamic SQL and omit the schema from the appended text, removing the need for quotes.