SQLServerCentral / Editorials / SQLServerCentral.com / Guest Editorial: In Praise of Templates / Latest Posts

RE: Guest Editorial: In Praise of Templates

Charles Kincaid — Mon, 09 Feb 2009 07:46:57 GMT

[quote][b]Phil Factor (2/7/2009)[/b][hr]David, I've been pestering the SQL Prompt team about this for years.[/quote]Here would be a booster to aid in acceptance. Since lots of us developers embed SQL into our code it would be nice to provide copy and paste functionality that would put the SQL code into a string variable. Someone sent me a jar file with a SQL formater that does this. I can copy my old code in, it strips it down to SQL, reformats the SQL statement, and lets me put the code wrapper back on.

RE: Guest Editorial: In Praise of Templates

Phil Factor — Sat, 07 Feb 2009 02:06:59 GMT

[quote]I use templates in conjunction with the SQL Prompt and the combination of the two is greater than the sum of the parts.What SQLPrompt needs is a few more constructs like their $CURSOR$ place marker.[/quote]David, I've been pestering the SQL Prompt team about this for years. Both snippets and templates are 'work in progress' and, with a bit of effort, could be finished off to provide all sorts of great aids to productivity. You can do wonderful things with this sort of technology.I wrote a template Wiki that actually executed the macros and allowed team workers to add and change templates from a central location. It used the Prettifier to display the templates nicely and you could browse through them very easily and search like lightening. The Devs didn't 'Get it'!

RE: Guest Editorial: In Praise of Templates

azoltay — Fri, 06 Feb 2009 17:10:31 GMT

We've been using templates at our organization for sometime now - love 'em. Great for getting consistency across db developers and enforcing standards. The one problem, which you mentioned, is the inability to share the templates in a central location. I'm hoping MS comes out with a solution in the near future. Cheers,Andy

RE: Guest Editorial: In Praise of Templates

sue-381329 — Fri, 06 Feb 2009 15:54:52 GMT

Thank you, Phil. I have had a rainy Friday afternoon of fun - thinking up new and exciting ways to use templates!! Once I got the hang of the newtemplate/edit/save, I realized that through simple cut and paste into notepad or word, I can build a directory of custom templates to hand out to team members or new installs.A great way to share standard formats....

RE: Guest Editorial: In Praise of Templates

David.Poole — Fri, 06 Feb 2009 15:31:35 GMT

This time last year I wrote about how to share templates within a team of DBAs http://www.sqlservercentral.com/articles/System+Development+Life+Cycle/62933/I use templates in conjuncton with the SQL Prompt and the combination of the two is greater than the sum of the parts.What SSMS needs is a simple macro language. Something a bit more than SQLCMD mode but not as heavy weight as powershell.What SQLPrompt needs is a few more constructs like their $CURSOR$ place marker.$DATE.YMD$ would be useful as would $USER$, particularly as my company shares the SQL Prompt 3 Snippets.XML file between DBAs.

RE: Guest Editorial: In Praise of Templates

sgardner — Fri, 06 Feb 2009 09:47:36 GMT

For most of my direct DB work I use SQuirreL SQL (http://www.squirrelsql.org/) It is a powerful database front end tool that runs across a wide range of databases. It has a rich set of plugins (that I don't take enough advantage of) as well as great out-of-the box functionality. It runs on Mac, Linux, Unix and windows. I find it much more productive than the MS Studio for quick database exploring and running ad-hoc scripts. It is really nice having the same tool no matter what machine and database I have to look at.

RE: Guest Editorial: In Praise of Templates

JJ B — Fri, 06 Feb 2009 09:23:38 GMT

I'm a big fan of template use. They do save so much time, especially when you use templates for the big stuff like stored procedures. However, since I store all my procs, functions, views, etc. in MS Word documents, I use MS Word templates for my personal on-going use. It works great. Here's a thought: One plus a Word template would have over SSMS would be that they can be stored on shared drives and used by a team instead of just one person.Just sharing in case someone else is curious how that option works.

RE: Guest Editorial: In Praise of Templates

Charles Kincaid — Fri, 06 Feb 2009 08:40:30 GMT

Good article Phil. I got to [quote]when some shinier bead rolled into view[/quote] and it put me in mind of my cats. :)Then I saw your create table template. :(

RE: Guest Editorial: In Praise of Templates

george sibbald — Fri, 06 Feb 2009 08:21:09 GMT

[quote][b]JoeA (2/6/2009)[/b][hr]Nope, it was for Mr. Icocks, who said his key combo didn't work in SSMS 2008 :)[/quote]:blush:

RE: Guest Editorial: In Praise of Templates

icocks — Fri, 06 Feb 2009 08:15:55 GMT

[quote]JoeA (2/6/2009)Is it possible that your Ctrl+Shift+M is being caught by another program?I had this issue with a similar key sequence where an HP diagnostic (or update manager?) was catching them as a hot key - never made it down into the window that had actual focus.I was able to override that sequence in the diagnostic S/W that was catching it.Happened on a different machine than the one I'm typing into, so I don't have exact details.was that aimed at me?[/quote]I believe it was aimed at me - for some reason Ctrl-Shift-M doesn't work for on my SSMS 2008 (though it was fine on 2005). Something intercepting it sounds pretty plausible - will investigate.Cheers,Ian

RE: Guest Editorial: In Praise of Templates

JoeA — Fri, 06 Feb 2009 08:14:22 GMT

Nope, it was for Mr. Icocks, who said his key combo didn't work in SSMS 2008 :)

RE: Guest Editorial: In Praise of Templates

george sibbald — Fri, 06 Feb 2009 08:09:40 GMT

[quote][b]JoeA (2/6/2009)[/b][hr]Is it possible that your Ctrl+Shift+M is being caught by another program?I had this issue with a similar key sequence where an HP diagnostic (or update manager?) was catching them as a hot key - never made it down into the window that had actual focus.I was able to override that sequence in the diagnostic S/W that was catching it.Happened on a different machine than the one I'm typing into, so I don't have exact details.[/quote]was that aimed at me? I'm not having problems with it, just knew I wouldn't remember that combination of keys. Thanks anyway. I''l butt out now so things can get back on topic. :)

RE: Guest Editorial: In Praise of Templates

JoeA — Fri, 06 Feb 2009 07:47:42 GMT

Is it possible that your Ctrl+Shift+M is being caught by another program?I had this issue with a similar key sequence where an HP diagnostic (or update manager?) was catching them as a hot key - never made it down into the window that had actual focus.I was able to override that sequence in the diagnostic S/W that was catching it.Happened on a different machine than the one I'm typing into, so I don't have exact details.

RE: Guest Editorial: In Praise of Templates

Phil Factor — Fri, 06 Feb 2009 07:13:43 GMT

Here is a sample of a customised template. There is a type of service table I use whose basic structure is pretty similar. I just delete the bits I don't need. It is far quicker than doing it by point 'n shoot.[font="Courier New"][size="2"][color="green"]-- =========================================-- Create table template-- =========================================[/color][color="blue"]USE [/color][color="gray"]<[/color][color="blue"]DATABASE[/color][color="gray"], [/color][color="blue"]SYSNAME[/color][color="gray"], [/color][color="black"]MyDatabase[/color][color="gray"]>[/color][color="black"]GO[/color][color="blue"]IF [/color][color="magenta"]OBJECT_ID[/color][color="gray"]([/color][color="red"]'<schema_name, sysname, dbo>.<table_name, sysname, sample_table>'[/color][color="gray"], [/color][color="red"]'U'[/color][color="gray"]) [/color][color="blue"]IS [/color][color="gray"]NOT NULL [/color][color="blue"]DROP TABLE [/color][color="gray"]<[/color][color="black"]schema_name[/color][color="gray"], [/color][color="blue"]SYSNAME[/color][color="gray"], [/color][color="black"]dbo[/color][color="gray"]>[/color][color="black"].[/color][color="gray"]<[/color][color="black"]table_name[/color][color="gray"], [/color][color="blue"]SYSNAME[/color][color="gray"], [/color][color="black"]sample_table[/color][color="gray"]>[/color][color="black"]GO[/color][color="blue"]CREATE TABLE [/color][color="gray"]<[/color][color="black"]schema_name[/color][color="gray"], [/color][color="blue"]SYSNAME[/color][color="gray"], [/color][color="black"]dbo[/color][color="gray"]>[/color][color="black"].[/color][color="gray"]<[/color][color="black"]table_name[/color][color="gray"], [/color][color="blue"]SYSNAME[/color][color="gray"], [/color][color="black"]sample_table[/color][color="gray"]>( [/color][color="black"][<table_name, sysname, sample_table>_id] [int] [/color][color="#434343"]IDENTITY[/color][color="gray"]([/color][color="black"]1[/color][color="gray"],[/color][color="black"]1[/color][color="gray"]) NOT NULL, [/color][color="black"][creator] [varchar][/color][color="gray"]([/color][color="black"]80[/color][color="gray"]) NOT NULL [/color][color="blue"]CONSTRAINT [/color][color="black"][DF_<table_name, sysname, sample_table>_creator] [/color][color="blue"]DEFAULT [/color][color="gray"]([/color][color="magenta"]USER_NAME[/color][color="gray"]()), [/color][color="black"][insertiondate] [datetime] [/color][color="gray"]NOT NULL [/color][color="blue"]CONSTRAINT [/color][color="black"][DF_<table_name, sysname, sample_table>_insertiondate] [/color][color="blue"]DEFAULT [/color][color="gray"]([/color][color="magenta"]GETDATE[/color][color="gray"]()), [/color][color="black"][terminationdate] [datetime] [/color][color="gray"]NULL [/color][color="blue"]CONSTRAINT [/color][color="black"]PK_[/color][color="gray"]<[/color][color="black"]schema_name[/color][color="gray"], [/color][color="blue"]SYSNAME[/color][color="gray"], [/color][color="black"]dbo[/color][color="gray"]>[/color][color="black"]_[/color][color="gray"]<[/color][color="black"]table_name[/color][color="gray"], [/color][color="blue"]SYSNAME[/color][color="gray"], [/color][color="black"]sample_table[/color][color="gray"]> [/color][color="blue"]PRIMARY KEY [/color][color="gray"](<[/color][color="black"]table_name[/color][color="gray"], [/color][color="blue"]SYSNAME[/color][color="gray"], [/color][color="black"]sample_table[/color][color="gray"]>[/color][color="black"]_id[/color][color="gray"]))[/color][color="black"]GO[/color][color="green"]-- Add description to table object[/color][color="blue"]EXEC [/color][color="black"]sys.[/color][color="darkred"]sp_addextendedproperty [/color][color="#434343"]@name[/color][color="blue"]=[/color][color="red"]N'MS_Description'[/color][color="gray"], [/color][color="#434343"]@value[/color][color="blue"]=[/color][color="red"]N'<table_description_value,,Table description here>' [/color][color="gray"], [/color][color="#434343"]@level0type[/color][color="blue"]=[/color][color="red"]N'SCHEMA'[/color][color="gray"], [/color][color="#434343"]@level0name[/color][color="blue"]=[/color][color="red"]N'<schema_name, sysname, dbo>'[/color][color="gray"], [/color][color="#434343"]@level1type[/color][color="blue"]=[/color][color="red"]N'TABLE'[/color][color="gray"], [/color][color="#434343"]@level1name[/color][color="blue"]=[/color][color="red"]N'<table_name, sysname, sample_table>'[/color][color="black"]GO[/color][color="green"]-- Add description to a specific column[/color][color="blue"]EXEC [/color][color="black"]sys.[/color][color="darkred"]sp_addextendedproperty [/color][color="#434343"]@name[/color][color="blue"]=[/color][color="red"]N'MS_Description'[/color][color="gray"], [/color][color="#434343"]@value[/color][color="blue"]=[/color][color="red"]N'primary key for the table' [/color][color="gray"], [/color][color="#434343"]@level0type[/color][color="blue"]=[/color][color="red"]N'SCHEMA'[/color][color="gray"], [/color][color="#434343"]@level0name[/color][color="blue"]=[/color][color="red"]N'<schema_name, sysname, dbo>'[/color][color="gray"], [/color][color="#434343"]@level1type[/color][color="blue"]=[/color][color="red"]N'TABLE'[/color][color="gray"], [/color][color="#434343"]@level1name[/color][color="blue"]=[/color][color="red"]N'<table_name, sysname, sample_table>'[/color][color="gray"], [/color][color="#434343"]@level2type[/color][color="blue"]=[/color][color="red"]N'COLUMN'[/color][color="gray"], [/color][color="#434343"]@level2name[/color][color="blue"]=[/color][color="red"]N'<table_name, sysname, sample_table>_id'[/color][color="black"]GO[/color][color="blue"]EXEC [/color][color="black"]sys.[/color][color="darkred"]sp_addextendedproperty [/color][color="#434343"]@name[/color][color="blue"]=[/color][color="red"]N'MS_Description'[/color][color="gray"], [/color][color="#434343"]@value[/color][color="blue"]=[/color][color="red"]N'Date the record was created (automatic)' [/color][color="gray"], [/color][color="#434343"]@level0type[/color][color="blue"]=[/color][color="red"]N'SCHEMA'[/color][color="gray"], [/color][color="#434343"]@level0name[/color][color="blue"]=[/color][color="red"]N'<schema_name, sysname, dbo>'[/color][color="gray"], [/color][color="#434343"]@level1type[/color][color="blue"]=[/color][color="red"]N'TABLE'[/color][color="gray"], [/color][color="#434343"]@level1name[/color][color="blue"]=[/color][color="red"]N'<table_name, sysname, sample_table>'[/color][color="gray"], [/color][color="#434343"]@level2type[/color][color="blue"]=[/color][color="red"]N'COLUMN'[/color][color="gray"], [/color][color="#434343"]@level2name[/color][color="blue"]=[/color][color="red"]N'insertiondate'[/color][color="black"]GO[/color][color="blue"]EXEC [/color][color="black"]sys.[/color][color="darkred"]sp_addextendedproperty [/color][color="#434343"]@name[/color][color="blue"]=[/color][color="red"]N'MS_Description'[/color][color="gray"], [/color][color="#434343"]@value[/color][color="blue"]=[/color][color="red"]N'Date for the termination of the record' [/color][color="gray"], [/color][color="#434343"]@level0type[/color][color="blue"]=[/color][color="red"]N'SCHEMA'[/color][color="gray"], [/color][color="#434343"]@level0name[/color][color="blue"]=[/color][color="red"]N'<schema_name, sysname, dbo>'[/color][color="gray"], [/color][color="#434343"]@level1type[/color][color="blue"]=[/color][color="red"]N'TABLE'[/color][color="gray"], [/color][color="#434343"]@level1name[/color][color="blue"]=[/color][color="red"]N'<table_name, sysname, sample_table>'[/color][color="gray"], [/color][color="#434343"]@level2type[/color][color="blue"]=[/color][color="red"]N'COLUMN'[/color][color="gray"], [/color][color="#434343"]@level2name[/color][color="blue"]=[/color][color="red"]N'Terminationdate'[/color][color="black"]GO[/color][color="blue"]EXEC [/color][color="black"]sys.[/color][color="darkred"]sp_addextendedproperty [/color][color="#434343"]@name[/color][color="blue"]=[/color][color="red"]N'MS_Description'[/color][color="gray"], [/color][color="#434343"]@value[/color][color="blue"]=[/color][color="red"]N'Creator of the record' [/color][color="gray"], [/color][color="#434343"]@level0type[/color][color="blue"]=[/color][color="red"]N'SCHEMA'[/color][color="gray"], [/color][color="#434343"]@level0name[/color][color="blue"]=[/color][color="red"]N'<schema_name, sysname, dbo>'[/color][color="gray"], [/color][color="#434343"]@level1type[/color][color="blue"]=[/color][color="red"]N'TABLE'[/color][color="gray"], [/color][color="#434343"]@level1name[/color][color="blue"]=[/color][color="red"]N'<table_name, sysname, sample_table>'[/color][color="gray"], [/color][color="#434343"]@level2type[/color][color="blue"]=[/color][color="red"]N'COLUMN'[/color][color="gray"], [/color][color="#434343"]@level2name[/color][color="blue"]=[/color][color="red"]N'creator'[/color][color="black"]GO[/color][/size][/font]

RE: Guest Editorial: In Praise of Templates

george sibbald — Fri, 06 Feb 2009 07:05:20 GMT

[quote][b]Andy Lennon (2/6/2009)[/b][hr]just think "Mmmm templates"[/quote]that'll work! cheers andy.

RE: Guest Editorial: In Praise of Templates

Andy Lennon — Fri, 06 Feb 2009 06:56:56 GMT

just think "Mmmm templates"

RE: Guest Editorial: In Praise of Templates

george sibbald — Fri, 06 Feb 2009 06:45:58 GMT

A useful feature I'd forgotten all about, thanks. Now how am i going to remember ctrl-shift-m?

RE: Guest Editorial: In Praise of Templates

Loner — Fri, 06 Feb 2009 05:55:02 GMT

Thanks Phil. It is a big help.

RE: Guest Editorial: In Praise of Templates

Andy sql — Fri, 06 Feb 2009 03:36:23 GMT

Thanks Phil,Templates have been a well-kept-secret from me. I have never used them. But I can see how they could really speed up some of my work. Much appreciated info.Andy

RE: Guest Editorial: In Praise of Templates

icocks — Fri, 06 Feb 2009 02:35:36 GMT

Interesting - I'm on the same version. The other shortcut combis all work ok though. I'd just assumed templates had been forgotten. hmmm...

RE: Guest Editorial: In Praise of Templates

Phil Factor — Fri, 06 Feb 2009 02:25:34 GMT

Hmm. Cntrl Shift M. It works on the version of SSMS 2008 I have here, but maybe my version is a bit old (10.0.1600.22 ). I'd be surprised if they took it out as it goes way back. Mind you, they irritated me hugely by taking out the hotkey that flipped between windows in 2005. There is a way of redefining these keys via the registry, but you'd need to be both brave and desperate.

RE: Guest Editorial: In Praise of Templates

icocks — Fri, 06 Feb 2009 01:54:41 GMT

One of the annoyances of SSMS 2008 is that Ctrl-Shift-M no longer works.

RE: Guest Editorial: In Praise of Templates

steve quinn — Fri, 06 Feb 2009 01:31:41 GMT

The supplied templates are also a great self-tuition tool for budding DBAs (and others! like moi for instance).I try to set aside some time every session to browse through the supplied templates and 'dissect' a particular example to figure out what it's for and how it does it.Had forgotten about the 'parameter filler' - thanks for the reminder!Best wishes.

Guest Editorial: In Praise of Templates

Phil Factor — Thu, 05 Feb 2009 20:59:31 GMT

Comments posted to this topic are about the item [B]Guest Editorial: In Praise of Templates[/B]