New Table Template

Is there a way to include some information as default fields in the create new table template for SQL Server?

In every single table we have
createdBy
createdDate
modifiedBy
modifiedDate
active

Thanks
Larry Bristersr. DeveloperAsked:
Who is Participating?
 
Randy DownsOWNERCommented:
Try this
http://www.techrepublic.com/article/experiencing-the-power-of-sql-templates/5653868


-- =============================================-- Basic Create Database Template-- =============================================
F EXISTS (SELECT * ....   FROM   master..sysdatabases....  
WHERE  name = N'<database_name, sysname, test_db>')....
DROP DATABASE <database_name, sysname, test_db>
GO
CREATE DATABASE <database_name, sysname, test_db>
GO
The angle brackets in the code denote replaceable markers.The Replace Template Parameters command on the QA Edit menu invokes a dialogbox that lets you replace all the parameters at one time. Note: Thenon-intuitive shortcut to open this dialog box is [Ctrl][Shift]M.

What may not be apparent is that you can combine severaltemplates into one stored procedure. Once your first template is in its editwindow, just drag any other template into the same window.

Many organizations have standards that govern the style,indentation, and header of their stored procedures. This is where you canexperience the real power of templates. You can customize existing templatessimply by loading them into your favorite text editor. Even better, you cancreate new folders in the SQL Query Analyzer folder and populate them with yourown templates. They'll show up automatically in the QA object browser. Thereare no templates for actions such as DELETE or UPDATE; however, you can rollyour own templates for such actions and add them to the SQL Query Analyzerfolder.

If you work in a multi-developer environment, you can tellQA to look elsewhere (such as a shared folder on the network) for itstemplates. From the QA menu, select Tools | Options and then edit the templatedirectory setting.

If you have yet to explore these templates, I encourage youto do so. You’ll find that you save a lot of typing, and, with a littlecustomization, you can adhere automatically to your organization’s standards.
..
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Let me know if this works for you.

IF OBJECT_ID('foo','U') IS NOT NULL
      begin
      DROP TABLE foo
      end
GO

CREATE TABLE [dbo].[foo](
      ID int identity(1,1) PRIMARY KEY NOT NULL,
      createdBy varchar(25),
      createdDate datetime,
      modifiedBy varchar(25),
      modifiedDate datetime,
      active bit)
GO


-- Add a description to the table
exec sp_addextendedproperty
      'MS_Description', 'Your table description goes here.',
      'schema', 'dbo', 'table', 'foo'

-- Add a description to a column
exec sp_addextendedproperty
      'MS_Description', 'Your column description goes here.',
      'schema', 'dbo', 'table', 'foo', 'column', 'createdBy'
      
-- Make GetDate the default value for CreatedBy
ALTER TABLE [dbo].[foo] ADD  DEFAULT (getdate()) FOR [createdBy]
GO
0
 
jogosCommented:
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Larry Bristersr. DeveloperAuthor Commented:
Hey guys...all three are great answers.
Number-1: was first...but how bout a 50.25.25  % split?
0
 
Randy DownsOWNERCommented:
fine by me
0
 
Larry Bristersr. DeveloperAuthor Commented:
Great answers.  Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.