Guest Editorial: In Praise of Templates

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQLServerCentral.com » Editorials » Guest Editorial: In Praise of Templates

24 posts, Page 2 of 3

««1 2 3 »»

Guest Editorial: In Praise of Templates

Rate Topic

Display Mode

Topic Options

Author

Message

Phil Factor

Posted Friday, February 06, 2009 7:13 AM

Mr or Mrs. 500

Group: General Forum Members
Last Login: Sunday, May 19, 2013 4:00 AM
Points: 533, Visits: 2,285

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.

-- =========================================
-- Create table template
-- =========================================
USE <DATABASE, SYSNAME, MyDatabase>
GO

IF OBJECT_ID('<schema_name, sysname, dbo>.<table_name, sysname, sample_table>', 'U') IS NOT NULL
  DROP TABLE <schema_name, SYSNAME, dbo>.<table_name, SYSNAME, sample_table>
GO
CREATE TABLE <schema_name, SYSNAME, dbo>.<table_name, SYSNAME, sample_table>
(
   [<table_name, sysname, sample_table>_id] [int] IDENTITY(1,1) NOT NULL,
   [creator] [varchar](80) NOT NULL CONSTRAINT [DF_<table_name, sysname, sample_table>_creator]  DEFAULT (USER_NAME()),
   [insertiondate] [datetime] NOT NULL CONSTRAINT [DF_<table_name, sysname, sample_table>_insertiondate]  DEFAULT (GETDATE()),
   [terminationdate] [datetime] NULL
    CONSTRAINT PK_<schema_name, SYSNAME, dbo>_<table_name, SYSNAME, sample_table> PRIMARY KEY (<table_name, SYSNAME, sample_table>_id)
)
GO
-- Add description to table object
EXEC sys.sp_addextendedproperty
   @name=N'MS_Description',
   @value=N'<table_description_value,,Table description here>' ,
   @level0type=N'SCHEMA',
   @level0name=N'<schema_name, sysname, dbo>',
   @level1type=N'TABLE',
   @level1name=N'<table_name, sysname, sample_table>'
GO

-- Add description to a specific column
EXEC sys.sp_addextendedproperty
   @name=N'MS_Description',
   @value=N'primary key for the table' ,
   @level0type=N'SCHEMA',
   @level0name=N'<schema_name, sysname, dbo>',
   @level1type=N'TABLE',
   @level1name=N'<table_name, sysname, sample_table>',
   @level2type=N'COLUMN',
   @level2name=N'<table_name, sysname, sample_table>_id'
GO
EXEC sys.sp_addextendedproperty
   @name=N'MS_Description',
   @value=N'Date the record was created (automatic)' ,
   @level0type=N'SCHEMA',
   @level0name=N'<schema_name, sysname, dbo>',
   @level1type=N'TABLE',
   @level1name=N'<table_name, sysname, sample_table>',
   @level2type=N'COLUMN',
   @level2name=N'insertiondate'
GO
EXEC sys.sp_addextendedproperty
   @name=N'MS_Description',
   @value=N'Date for the termination of the record' ,
   @level0type=N'SCHEMA',
   @level0name=N'<schema_name, sysname, dbo>',
   @level1type=N'TABLE',
   @level1name=N'<table_name, sysname, sample_table>',
   @level2type=N'COLUMN',
   @level2name=N'Terminationdate'
GO
EXEC sys.sp_addextendedproperty
   @name=N'MS_Description',
   @value=N'Creator of the record' ,
   @level0type=N'SCHEMA',
   @level0name=N'<schema_name, sysname, dbo>',
   @level1type=N'TABLE',
   @level1name=N'<table_name, sysname, sample_table>',
   @level2type=N'COLUMN',
   @level2name=N'creator'
GO

Best wishes,

Phil Factor
Simple Talk

Post #651628

JoeA

Posted Friday, February 06, 2009 7:47 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 7:44 AM
Points: 76, Visits: 714

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.

Post #651673

george sibbald

Posted Friday, February 06, 2009 8:09 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 5,270, Visits: 11,211

JoeA (2/6/2009)

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. :)

---------------------------------------------------------------------

Post #651697

JoeA

Posted Friday, February 06, 2009 8:14 AM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 7:44 AM
Points: 76, Visits: 714

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

Post #651706

icocks

Posted Friday, February 06, 2009 8:15 AM

SSC Rookie

Group: General Forum Members
Last Login: Thursday, April 18, 2013 8:11 AM
Points: 46, Visits: 565

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?

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

Post #651709

george sibbald

Posted Friday, February 06, 2009 8:21 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 9:09 AM
Points: 5,270, Visits: 11,211

JoeA (2/6/2009)

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

---------------------------------------------------------------------

Post #651718

Charles Kincaid

Posted Friday, February 06, 2009 8:40 AM

Right there with Babe

Group: General Forum Members
Last Login: Friday, May 17, 2013 9:18 AM
Points: 772, Visits: 1,825

Good article Phil. I got to

when some shinier bead rolled into view

and it put me in mind of my cats. :)

Then I saw your create table template. Sad

ATB

Charles Kincaid

Post #651746

JJ B

Posted Friday, February 06, 2009 9:23 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 21, 2013 3:08 PM
Points: 255, Visits: 2,407

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.

Post #651791

sgardner

Posted Friday, February 06, 2009 9:47 AM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 19, 2009 3:59 PM
Points: 5, Visits: 31

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.

Post #651812

David.Poole

Posted Friday, February 06, 2009 3:31 PM

SSCrazy

Group: General Forum Members
Last Login: Today @ 8:46 AM
Points: 2,750, Visits: 1,410

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.

LinkedIn Profile

Post #652050

« Prev Topic | Next Topic »

24 posts, Page 2 of 3

««1 2 3 »»

Permissions