--
=============================================
-- Author: John Roth
-- Create date: 2010
-- Modified: August
14, 2013
-- Modified: July 10,
2013
-- Description:
Order in which to create tables to get the most out of this store
procedure is:
-- create a tbUser
table with simple id
-- create an lkp
table ending with the name Type and with NO "Usertable specified"
(e.g. lkpStoreType)
-- create a tb
table (e.g. tbStore, tbOwner, tbProduct) WITH "Usertable specified"
-- create an idx
table (e.g. idxStore_Product) WITH "Usertable specified"
-- Example:
-- USE [Redbull]
-- GO
-- DECLARE @return_value int
-- EXEC @return_value =
[development].[CreateNewTable_Prefix_withSimpleIdOption]
-- @TablePrefix =
N'idx',
-- @NewTableName =
N'Store_Product',
-- @SimpleId = 1,
-- @FullUserTableNameWithSchema
= N'dbo.tbUser'
--SELECT 'Return Value' = @return_value
--GO
-- To Do: 1. detect prefix (LKP, IDX, TB) and add
constraints automatically.
-- LKP: (create a unique
index on the value column)
-- IDX: (don't create a
value column, instead create a <table1>Id and <table2>Id bigint
columns based on the names of the idx table
-- (e.g.
idx<table1>_<table2> and could create relations if the tables
already exist.
-- TB: (create a type (bigint) column, and a unique
constraint on value).
--
-- NOTE: I suppose that
"code first" development would take care of all of this :), but there
are still some "code first" nuances (enums) that still are quite
right in my book.
--
--
-- 2. if a user tables exists or is specified you
could auto create a relationship with the createby or modifiedby columns
--
=============================================
ALTER PROCEDURE
[development].[CreateNewTable_Prefix_withSimpleIdOption]
-- Add the parameters for the stored procedure here
@TableSchema nvarchar(100) = null,@TablePrefix nvarchar(100) = '',@NewTableName nvarchar(150),@SimpleId bit = 0,@FullUserTableNameWithSchema
nvarchar(500)=null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
set @TablePrefix
= lower(@TablePrefix)
declare @command
nvarchar(max),@IdName nvarchar(200),@TableName nvarchar(500),@table1 nvarchar(150),@table2 nvarchar(150),@delim char(1),@index int
set @delim
= '_'
if @SimpleId
= 1
begin
set @IdName = 'Id'
end
else
begin
set @IdName = @NewTableName +'Id'
end
if @TablePrefix='idx'
begin
set @index = charindex(@delim,@NewTableName)
if @index > 0
begin
set @table1 = substring(@NewTableName,0,@index)
set @table2 = substring(@NewTableName,@index+1,1000)
--propper case it
set @table1 =upper(substring(@table1,1,1))+lower(substring(@table1,2,1000))
set @table2 =upper(substring(@table2,1,1))+lower(substring(@table2,2,1000))
end
end
set @TableName
= '['+isnull(@TableSchema,'dbo')+'].['+@TablePrefix+@NewTableName+']'
set @command='
CREATE TABLE '+@TableName+'(
['+@IdName+'] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Created] [datetime] NOT NULL,
[Modified] [datetime] NOT NULL,
[CreatedBy] [bigint] NOT NULL,
[ModifiedBy] [bigint] NOT NULL,'
+iif(@TablePrefix='idx','['+@table1+'Id] [bigint] NOT NULL, ['+@table2+'Id] [bigint] NOT NULL,','[Value] [nvarchar](150) NULL,')
+iif(@TablePrefix='tb','[Type] [bigint] NULL,','')+'
CONSTRAINT [PK_'+@TablePrefix+@NewTableName+'] PRIMARY KEY CLUSTERED
(
['+@IdName+'] 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 '+@TableName+' ADD CONSTRAINT ['+@NewTableName+'_Created] DEFAULT
(getdate()) FOR [Created]'
exec sp_executesql @command
set @command='ALTER TABLE '+@TableName+' 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 '+@TableName+'
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
declare @ID bigint
select @ID=['+@IdName+'] from inserted
UPDATE '+@TableName+'
SET [Modified] = getdate()
WHERE ['+@IdName+']=@ID
END
'
EXEC sp_executesql @command
-------------CREATING
UNIQUE INDEXES-------------------------
--create unique
non-custered index on value for tb and lkp type tables (naming convention is
20130809-110003... err date and time)
--http://www.sql-server-helper.com/tips/date-formats.aspx
declare @NonclusterIndexName
nvarchar(100)
set @NonclusterIndexName
= '[NonClusteredIndex'+'-'+CONVERT(VARCHAR(8), GETDATE(), 112)+'-'+replace(CONVERT(VARCHAR(8), GETDATE(), 108),':','')+']'
if @TablePrefix
= 'idx'
begin
set @command='CREATE UNIQUE NONCLUSTERED
INDEX '+@NonclusterIndexName+' ON '+@TableName+'
(
['+@table1+'Id] ASC,
['+@table2+'Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
end
else
begin
set @command='CREATE UNIQUE NONCLUSTERED
INDEX '+@NonclusterIndexName+' ON '+@TableName+'
(
[Value] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
end
EXEC sp_executesql @command
-------------------CREATING
RELATIONSHIP CONSTRAINTS (if tables exist and has the same schema - note: could
add a relationshipSchema parameter)----------------
/*
setup type
relationship between LKP and TB tables (if LKP table exists)
setup index
relationship between IDX and 2 TB tables (if 2 TB tables exists)
setup created and
modified relationship between and table and a USER table (if USER table is
specified)
*/
declare @CheckTableName1
nvarchar(500),@CheckTableName2 nvarchar(500), @ConstraintName nvarchar(500)
--PRINT
iif(object_id('[document].tbForm', 'U') is not null, 'Present!','Not accounted for')
/**** create lkp/type
table to regular table relationshiop ****/
if @TablePrefix
= 'tb' begin
set @CheckTableName1 = '['+isnull(@TableSchema,'dbo')+'].[lkp'+@NewTableName+'Type]'
if object_id(@CheckTableName1, 'U') is not null begin --if table exists
--create relationship
set @ConstraintName = '[FK_tb'+@NewTableName+'_lkp'+@NewTableName+'Type]'
set @command='ALTER TABLE '+@TableName+' WITH CHECK ADD CONSTRAINT '+@ConstraintName+' FOREIGN KEY([Type])
REFERENCES '+@CheckTableName1+' (['+iif(@SimpleId=1,'',@NewTableName+'Type')+'Id])'
EXEC sp_executesql @command
--verify relationship
set @command='ALTER TABLE '+@TableName+' CHECK CONSTRAINT '+@ConstraintName
EXEC sp_executesql @command
end
end
else if
@TablePrefix = 'idx' begin
set @CheckTableName1 = '['+isnull(@TableSchema,'dbo')+'].[tb'+@table1+']'
set @CheckTableName2 = '['+isnull(@TableSchema,'dbo')+'].[tb'+@table2+']'
if ((object_id(@CheckTableName1, 'U') is not null) and (object_id(@CheckTableName2, 'U') is not null)) begin --if table exists
--note should also check the id(primary key columns if they
exist)
--create relationship
set @ConstraintName = '[FK_idx'+@NewTableName+'_tb'+@table1+']'
set @command='ALTER TABLE '+@TableName+' WITH CHECK ADD CONSTRAINT '+@ConstraintName+' FOREIGN KEY(['+@table1+'Id])
REFERENCES '+@CheckTableName1+' (['+iif(@SimpleId=1,'',@table1)+'Id])'
EXEC sp_executesql @command
--verify relationship
set @command='ALTER TABLE '+@TableName+' CHECK CONSTRAINT '+@ConstraintName
EXEC sp_executesql @command
set @ConstraintName = '[FK_idx'+@NewTableName+'_tb'+@table2+']'
set @command='ALTER TABLE '+@TableName+' WITH CHECK ADD CONSTRAINT '+@ConstraintName+' FOREIGN KEY(['+@table2+'Id])
REFERENCES '+@CheckTableName2+' (['+iif(@SimpleId=1,'',@table2)+'Id])'
EXEC sp_executesql @command
--verify relationship
set @command='ALTER TABLE '+@TableName+' CHECK CONSTRAINT '+@ConstraintName
EXEC sp_executesql @command
end
end
/*****************
CREATE RELATIONSHIP WITH USER TABLE AND "CREATE BY" AND "MODIFY
BY" RELATIONSHIP ***************************/
IF @FullUserTableNameWithSchema
is not null BEGIN
set @ConstraintName = '[FK_idx'+@NewTableName+'_tbUser_CreatedBy]'
--assumes either UserId or Id... this should be more
flexible (e.g. should be aware of the User Table Name to generate
<usertablenameid>
set @command='ALTER TABLE '+@TableName+' WITH CHECK ADD CONSTRAINT '+@ConstraintName+' FOREIGN KEY([CreatedBy])
REFERENCES '+@FullUserTableNameWithSchema+' (['+iif(@SimpleId=1,'','User')+'Id])'
EXEC sp_executesql @command
--verify relationship
set @command='ALTER TABLE '+@TableName+' CHECK CONSTRAINT '+@ConstraintName
EXEC sp_executesql @command
set @ConstraintName = '[FK_idx'+@NewTableName+'_tbUser_ModifiedBy]'
--assumes either UserId or Id... this should be more
flexible (e.g. should be aware of the User Table Name to generate
<usertablenameid>
set @command='ALTER TABLE '+@TableName+' WITH CHECK ADD CONSTRAINT '+@ConstraintName+' FOREIGN KEY([ModifiedBy])
REFERENCES '+@FullUserTableNameWithSchema+' (['+iif(@SimpleId=1,'','User')+'Id])'
EXEC sp_executesql @command
--verify relationship
set @command='ALTER TABLE '+@TableName+' CHECK CONSTRAINT '+@ConstraintName
EXEC sp_executesql @command
END
No comments:
Post a Comment