Features from SSMS ToolsPack
There is a tool called SSMS ToolsPack (http://www.ssmstoolspack.com) which contains a number of useful tools, such as color-coding windows (which is now an experimental feature in SQLPrompt, but the other set allows you to choose your colors). One of the tools I use with unfortunate frequency (and has saved my bacon numerous times) is their "Query Execution History" feature; this saves every single query you execute with connection information, date/time stamp, and what file name it was executed from. If you've ever had to go "What was that ad-hoc query I wrote but didn't save?" this feature can help out, as long as you have an idea of when you ran it, or even just searching through the history for what you ran.
Thanks for your suggestions.
If there are specific features you would like to see added to Prompt, could you please raise individual requests for them? For instance, here is one for “Query Execution History”:
The Prompt Team
I am keen for tool pack features too, especially #region tags
@Justin @Stephen - Here at Red Gate we totally agree that query execution history and scripting of INSERTS are great features and well worth the additional €25 for the SSMS Tools Pack! @Jason - Tab History will be a fully integrated feature in SQL Prompt. It's not quite the same as an execution history but for some use cases serves a similar purpose.
The scripting of INSERTs from the results grid is a great feature that isn't available AFAIK anywhere else, and the "Find in Results" is really helpful, too.
+3 for these two features!
Jason Clements commented
I think this can be marked complete for the "Query Execution History". SQL Tab Magic 2012 is very close to the same. The snippet manager is getting closer to custom scripting but SSMS Tools pack custom scripting is nice that I can do it on any column, table, view, etc.
Vote from me for the Query Execution History, especially now that SSMSTools requires a licence for SQL2012!!
Jason Clements commented
I love the custom scripting.
Examples that I use everyday.
Add Extended Properties
DECLARE @type varchar(50), @timestamp DATETIME
SELECT @type=CASE WHEN [type] = 'U'
WHEN [type] = 'V'
WHEN [type] = 'P'
WHEN [type] = 'PK'
WHEN [TYPE] = 'D'
WHEN [type] = 'FN'
WHERE [NAME] = '|ObjectName|'
EXEC sys.sp_addextendedproperty @name=N'Created by', @value=N'Jason Clements' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=@type,@level1name=N'|ObjectName|'
EXEC sys.sp_addextendedproperty @name=N'Purpose', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=@type,@level1name=N'|ObjectName|'
EXEC sys.sp_addextendedproperty @name=N'Created on', @value=@timestamp, @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=@type,@level1name=N'|ObjectName|'
declare @tab table (id int identity(1,1) primary key,SQLname varchar(50))
insert into @tab(SQLname)
WHERE DATA_TYPE IN ('datetime', 'smalldatetime')
AND TABLE_NAME = (N'|ObjectName|')
declare @sql varchar(8000), @i int,@count int, @sqlname varchar(50)
select @count= count(*) from @tab
while @i <= @count
select @SQLname=SQLname from @tab where id=@i
set @sql = '
SELECT MAX('+@SQLname+') AS Max_Date_'+@SQLname+'
Lawrence Meklemburg commented
And I agree with what both state, but SSMS ToolsPack has gone for a pay for software direction. I would suggest that RedGate work with them to allow purchasers of SQL Prompt Pro to use the newer version for free.
Thanks for the comment. We've actually had the query history on our backlog for a while, even before it appeared in the SSMS Tools Pack. To avoid feature overlap we've focused on other things for now. There should be no reason why you can't use both tools alongside each other. We'll definitely considering adding in sometime in future, but it's not an immediate priority for now.