Tuesday, June 15, 2010

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'

No comments: