g00p3k
  • Posts: 31
  • Joined: 8/6/2012
I have a snippet I've created that when I designate the name of a table as the parameter value, will provide me back an "insert into" snippet.

For example:
declare @SchemaTable = 'schema.tableName'


insert into schema.tableName (
name
,description
,process_Id	-- default: ((0))
,process_type_id	-- default: ((0))
,last_start_date
,last_end_date
,as_of_date
,create_date	-- default: (getdate())
,delete_date
)


What I'd like to know if we can create now, or add to list is the ability to save the text of a script so that whatever I highlight/store, could be passed as the parameter value through to the script.

For example: Current SSMS allows:
Control-f5: select top(25) * from [here the selected text in SSMS gets appended and runs]

To extended this, I would love the feature to select my table name in the editor, and pass the variable name through to my script, even if it isn't appended to the end:
Control-Shift-I :



declare @SchemaTable varchar(100) = 'process.process_steps'


;with info as (
select [INSERT INTO] = 'insert into ' + @SchemaTable + ' (' 
,[SQID ORDINAL] = 0 
UNION ALL 
select
	--column_name
	[INSERT INTO] = i.COLUMN_NAME
	,[SQID ORDINAL] = row_number() over(order by i.ordinal_position asc) 
from
	information_schema.columns i with (nolock)
	
	--left join INFORMATION_SCHEMA.KEY_COLUMN_USAGE pk
	--on i.TABLE_CATALOG = pk.TABLE_CATALOG
	--and i.TABLE_NAME = pk.TABLE_NAME
	--and i.COLUMN_NAME = pk.COLUMN_NAME	

	left join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE c
	on i.TABLE_CATALOG = c.TABLE_CATALOG
	and i.TABLE_NAME = c.TABLE_NAME
	and i.COLUMN_NAME = c.COLUMN_NAME
where
	i.TABLE_SCHEMA + '.' + i.TABLE_NAME = @SchemaTable
	and (c.CONSTRAINT_NAME is null or left(c.constraint_name,2) = 'FK')
UNION ALL 
select [INSERT INTO] = ')'
,[SQID ORDINAL] = null 


)
select * from info




Yes, this function could be achieved by creating a stored proc in each database, or by creating in the master database, but to script would be much better! Most DBA's don't want user scripts in the master, and this would keep each persons personal "dynamic" statements local to their own creation. It could replace the need for many "intellisense" improvement engines that only really help by creating insert or procedure templates with list of parameters. All this information could be extracted out on our own.

Is there a current way to achieve this in SSMS boost or at least consider it for future dev?
Andrei
  • Posts: 398
  • Joined: 7/1/2012
Reviewing your request I unfortunately can not undestand what you mean. You have written a lot, so no need to re-write everything. Maybe explain shortly what this feature should simplify ? Maybe some part of it is already implemented ? Please provide a bit more info. Thanks !!
RayNeverLearns
  • Posts: 1
  • Joined: 2/9/2018
Almost 5 years later, I still don't see this feature and have the same question as goop3k.

A perfect example of what I believe he means and I definitely would like to see is the Favorites feature in Toad for SQL Server. As you can see below, in Toad you can save shortcuts to simple queries you write and you plan to reuse later, but don't merit saving as a script file. These don't even have to be full queries. They could be just a typical set of joins, for example. You can even organize these in folders and sub-folders (unlimited nesting).

UserPostedImage

Are there any plans to add this feature to future versions of SSMSBoost? It's the only thing holding us back from purchasing it. We are looking to have this feature within SSMS.

Thank you.
  • You cannot post new topics in this forum.
  • You cannot reply to topics in this forum.
  • You cannot delete your posts in this forum.
  • You cannot edit your posts in this forum.
  • You cannot create polls in this forum.
  • You cannot vote in polls in this forum.