Table Template

USE [PrinterUsage]
GO
/****** Object:  StoredProcedure [shared].[CreateNewTable]    Script Date: 06/30/2010 11:08:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:  
-- Create date: 
-- Description: 
-- =============================================
ALTER PROCEDURE [CreateNewTable] 
 -- Add the parameters for the stored procedure here
 @TableSchema nvarchar(100) = null,@NewTableName nvarchar(150)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;

    -- Insert statements for procedure here


declare @command nvarchar(max)
set @command='
CREATE TABLE ['+isnull(@TableSchema,'dbo')+'].['+@NewTableName+'](
 ['+@NewTableName+'Id] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL, 
 [Created] [datetime] NOT NULL,
 [Modified] [datetime] NOT NULL,
 CONSTRAINT [PK_'+@NewTableName+'] PRIMARY KEY CLUSTERED 
(
 ['+@NewTableName+'Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
'
exec sp_executesql @command


SET ANSI_PADDING OFF


set @command='ALTER TABLE ['+isnull(@TableSchema,'dbo')+'].['+@NewTableName+'] ADD  CONSTRAINT ['+@NewTableName+'_Created]  DEFAULT (getdate()) FOR [Created]'

exec sp_executesql @command


set @command='ALTER TABLE ['+isnull(@TableSchema,'dbo')+'].['+@NewTableName+'] ADD  CONSTRAINT ['+@NewTableName+'_Updated]  DEFAULT (getdate()) FOR [Modified]'
exec sp_executesql @command



SET ANSI_NULLS ON


SET QUOTED_IDENTIFIER ON

set @command='



CREATE TRIGGER [UpDate_'+@NewTableName+']
   ON  ['+isnull(@TableSchema,'dbo')+'].['+@NewTableName+']
   AFTER UPDATE
AS 
BEGIN
 SET NOCOUNT ON;

 declare @ID int

 select @ID=['+@NewTableName+'ID] from inserted
    
 UPDATE ['+isnull(@TableSchema,'dbo')+'].['+@NewTableName+']
 SET [Modified] = getdate()
 WHERE  ['+@NewTableName+'ID]=@ID

END

'
EXEC sp_executesql @command

END

Usage:

EXEC @return_value = [dbo].[CreateNewTable]
  @TableSchema = N'mySchema',
  @NewTableName = N'myTable'

or

EXEC @return_value = [dbo].[CreateNewTable]
  @NewTableName = N'myTable'