Comments
8 comments
-
Tony, I don't really use the pre-developed ones, but I do tend to take the templates I have created over the years and as I use them convert them to snippets, typically with a tf_ prefix so I know all mine are together if I need to port elsewhere. I leave the parameterization for templates intact so I can use the ctl+shift+m shortcut after the snippet drops into my query window. I also have multiple sp_whoisactive snippets with different parameters in place so I don't need to look up Adam's syntax in the middle of a crisis.
-
Hey Tim, thanks for your response - and good to hear from you. Hope all is well! Yes, I think most people just save the snippets they really need, for those times when someone is standing over your shoulder in a "is it fixed yet?" kind of way. I think our idea was that while SSMS templates are grouped by type of object, the Prompt Snippet library would be organized into different types of development task. Consistent naming is actually an interesting issue is we attempt to expand the snippet library - I'm forever forgetting the shortcut/initialism I've given a snippet.
-
i love snippets and use them all the time.
the custom ones i have are usually specific to the company i work for although i do always create one called 'srow' which brings up a query to give me a row count for every table in the database (taken from mssqltips).<div>SELECT QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS TableName,</div><div> SUM(sPTN.rows) AS [RowCount]</div><div>FROM sys.objects AS sOBJ</div><div> INNER JOIN sys.partitions AS sPTN</div><div> ON sOBJ.object_id = sPTN.object_id</div><div>WHERE sOBJ.type = 'U'</div><div> AND sOBJ.is_ms_shipped = 0x0</div><div> AND sPTN.index_id < 2 -- 0:Heap, 1:Clustered</div><div>GROUP BY sOBJ.schema_id,</div><div> sOBJ.name</div><div>ORDER BY 2 DESC;</div><div>GO</div>
i also have a simple one called 'sben' which i use all the time when creating ad-hoc data extracts for people.SELECT @@SERVERNAME AS Server, DB_NAME() AS DB, GETDATE() AS TimeRun<br>
The ones i use the most are
ssf, scf, st (use these day in, day out)
cci, cni (for clustered, non-clustered indexes)
ii - (the insert into one saves me so much time)
The only ones I think may be missing are for the window functions, they are simple enough but they have a funny syntax with a load of brackets... plus i'm lazy.SELECT ROW_NUMBER() OVER(PARTITION BY [column1],[column2]... ORDER BY [column1],[column2] DESC) AS 'ColumnName'
I never use the SSMS templates - have tried in the past but didn't find them useful personally.
I like the idea of having them around sp_who2 etc - maybe one that dumps the results into a table that you can then use a WHERE clause on??
cheers
Ben
-
Don't forget there is a github repo of snippets people had been contributing to, https://github.com/gvohra/sqlpromptsnippets. Maybe RedGate needs to have an "official" repo?
-
Which of the current snippets could be safely sent for recycling?
-
There are some odd ones in there - "neo" can go for starters.
-
@ben_b. Thanks for that - i now have a new rrc snippet (report row count)! So, on the topic of a consistent naming convention for snippets, which will be important if we're building out a more extensive library, what do people think? One proposal so far (courtesy of @Phil_Fact0r) is that snippets always start with a verb describing the action:
alter, add, change, create, enable, modify, drop, disable, insert, create, check, execute, find, report.
Then you identify the target object using the standard short code:- AF = Aggregate function (CLR)
- C = CHECK constraint
- D = DEFAULT (constraint or stand-alone)
- F = FOREIGN KEY constraint
- FN = SQL scalar function
- FS = Assembly (CLR) scalar-function
- FT = Assembly (CLR) table-valued function
- IF = SQL inline table-valued function
- IT = Internal table
- P = SQL Stored Procedure
- PC = Assembly (CLR) stored-procedure
- PG = Plan guide
- PK = PRIMARY KEY constraint
- R = Rule (old-style, stand-alone)
- RF = Replication-filter-procedure
- S = System base table
- SN = Synonym
- SO = Sequence object
- U = Table (user-defined)
- V = View
- EC = Edge constraint
- SQ = Service queue
- TA = Assembly (CLR) DML trigger
- TF = SQL table-valued-function
- TR = SQL DML trigger
- TT = Table type
- UQ = UNIQUE constraint
- X = Extended stored procedure
- ET = External Table
So, cTT (create table type), chXA (change xact-abort setting) and so on. Thoughts? -
I use snippets very similarly to Tim - i.e., i don't much use the built-in snippets, but I do:
- convert my own heavily re-used/re-issued 'commands' into scripts/snippets and
- commonly 'interleave' SQL Prompt snippets with t-sql template paramters (i.e.,CTRL+SHIFT+M) - as that provides me with the 'idiomatic' syntax AND the ability to quickly/easily 'customize' or parameterize on the fly.
And, when I say 'idiom' - I'm referring to how Ken Henderson used the term in his Guru's Guide to SQL Server Sprocs/etc. where his idea/goal of an 'idiom' was to find the most-correct and most-succinct version of a set of commands/directives.
Or, in other words, if you look at how good 'scripters' work, they:
- initially start by issuing commands 'manually' or 'from scratch' until they gain enough comfort and familiarity with the most COMMON ways those commands are used to address their needs then,
- they save 'persisted' versions of those commands as SCRIPTS for easier re-use.
Seems to me that Tim and I are using this functionality more as a tool to help us with 'scripting' than as a dev tool/aid (though there's no reason it couldn't be used in that sense - obviously). My thought/idea being that if Redgate were to host an 'official' repo full of snippets, a great guiding principle or idea for those snippets would be that:
- they weren't just practical or 'time savers'
- they were idiomatic - i.e., representing a 'best of breed' approach to tackling what's being done (easier said than done, obviously).
And, if that were the case, it seems that such an endeavor/initiative would pair NICELY with current Code Analysis functionality and efforts.
Add comment
Please sign in to leave a comment.
Dear FoRG Prompt users,
For those who don't know, I'm an editor at Redgate, responsible for our technical 'how-to' articles for Product Learning. I've been thinking about Prompt snippets, both how Redgate can make them more useful, and possible articles to illustrate their uses. I'm interested to know if, and how, you use the snippets feature.
Do you use the built-in snippets? Which ones? For example, do you use built-in snippets for create table, alter table etc., or do you tend to get most of those "direct from source", via SSMS templates? We've made one or two attempts to customize the built-in Prompt snippets, so they offer substantially more than the SSMS templates. See for example: Building a better ALTER TABLE ADD snippet. Is that useful? Have any of you done any similar customizations?
Do you build your own custom snippets for some of your favorite routines? For what sort of tasks, typically? Phil Factor has done various articles showing how to use snippets to create useful, adaptable routines, a prime example being his chk snippet to get a list of all the SQL statements executed within a batch, their execution plans and statistics.
With the versatility that snippets offer through the various placeholders, this is perhaps where there is most 'mileage' in expanding the snippets feature, so that it becomes more like a library of building blocks for common routines, and for exploring database metadata, quickly.
Would this be useful? What sort of custom snippets might it include?
Look forward to your thoughts. Also, if you've created any custom snippets, or have some that you think are strong candidates, and are willing to write an article about it, I'd love to hear from you.
Cheers,
Tony.