Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Guest Editorial: In Praise of Templates Expand / Collapse
Author
Message
Posted Friday, February 6, 2009 7:13 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 5:03 AM
Points: 579, Visits: 2,520
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
Posted Friday, February 6, 2009 7:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 8:47 AM
Points: 76, Visits: 756
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
Posted Friday, February 6, 2009 8:09 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:52 PM
Points: 5,988, Visits: 12,923
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'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
Posted Friday, February 6, 2009 8:14 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, March 12, 2014 8:47 AM
Points: 76, Visits: 756
Nope, it was for Mr. Icocks, who said his key combo didn't work in SSMS 2008 :)
Post #651706
Posted Friday, February 6, 2009 8:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, August 8, 2014 2:06 AM
Points: 47, Visits: 599
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
Posted Friday, February 6, 2009 8:21 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, August 21, 2014 3:52 PM
Points: 5,988, Visits: 12,923
JoeA (2/6/2009)
Nope, it was for Mr. Icocks, who said his key combo didn't work in SSMS 2008 :)




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

Post #651718
Posted Friday, February 6, 2009 8:40 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, February 6, 2014 12:59 PM
Points: 801, Visits: 1,962
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.


ATB

Charles Kincaid

Post #651746
Posted Friday, February 6, 2009 9:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:53 PM
Points: 266, Visits: 2,589
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
Posted Friday, February 6, 2009 9:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Friday, February 6, 2009 3:31 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:07 AM
Points: 2,901, Visits: 1,805
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
Newbie on www.simple-talk.com
Post #652050
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse