Create And Execute Table Script (SQL Server 2000)

/*
Created by: Robert W. Marda
When completed: 11 Oct 2002

Purpose:  Build script to create a table with default values 
and/or its primary key and/or its indexes.
*/CREATE PROCEDURE sp_CreateAndExecTableScript 
(@TableName varchar(255) = '', 
@TableNameExt varchar(10) = '', 
@DisplayScript bit = 0, 
@Exec bit = 0, 
@NoPK bit = 0, 
@PKOnly bit = 0, 
@NoIndexes bit = 0, 
@NoTable bit = 0
)AS

SET NOCOUNT ON

--Begin invalid entries for parameters section

--Test for empty entry
IF @TableName = ''
BEGIN
PRINT '@TableName is a required parameter.'

RETURN 1
END

--Test for source table
IF NOT EXISTS (select * from sysobjects where id = object_id(N'[dbo].[' + @TableName + ']') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'Table ' + @TableName + ' not found.'

RETURN 2
END

--End invalid entries for parameters section

DECLARE @Query varchar (8000),
@DFQuery varchar(8000)

SET @Query = ''
SET @DFQuery = ''

--Begin building datetime value to be used to ensure primary key and index names are unique
DECLARE @DateTime varchar(20)
DECLARE @rawDateTime varchar(20)

SET @rawdatetime = CURRENT_TIMESTAMP
SET @DateTime = SUBSTRING(@rawDateTime, 5, 2) + LEFT(@rawDateTime, 3) + SUBSTRING(@rawDateTime, 8, 4)

IF SUBSTRING(@rawDateTime, 13, 1) = ' '
SET @DateTime = @DateTime + SUBSTRING(@rawDateTime, 14, 1)
ELSE
SET @DateTime = @DateTime + SUBSTRING(@rawDateTime, 13, 2)

SET @DateTime = LTRIM(@DateTime) + '_' + SUBSTRING(@rawDateTime, 16, 4)
--End building datetime value

--Begin creating temp tables

--temp table #TableScript is used to gather data needed to generate script that will create the table
IF @NoTable = 0 AND @PKOnly = 0
CREATE TABLE #TableScript (
ColumnName varchar (30),
DataType varchar(40),
Length varchar(4),
[Precision] varchar(4),
Scale varchar(4),
IsNullable varchar(1),
TableName varchar(30),
ConstraintName varchar(255),
DefaultValue varchar (255),
GroupName varchar(35),
collation sysname NULL,
IdentityColumn bit NULL
)

--temp table #IndexScript is used to gather data needed to generate script that will create indexes for table
CREATE TABLE #IndexScript (
IndexName varchar (255),
IndId int,
ColumnName varchar (255),
IndKey int,
UniqueIndex int
)

--End creating temp tables

--Begin filling temp table #TableScript
IF @NoTable = 0 AND @PKOnly = 0
BEGIN

INSERT INTO #TableScript (ColumnName, DataType, Length, [Precision], Scale, IsNullable, TableName, 
  ConstraintName, DefaultValue, GroupName, collation, IdentityColumn)
SELECT  LEFT(c.name,30) AS ColumnName, 
LEFT(t.name,30) AS DataType, 
CASE t.length
WHEN 8000 THEN c.prec  --This criteria used because Enterprise Manager delivers the length in parenthesis for these datatypes when using its scripting capabilities.
ELSE NULL
END AS Length, 
CASE t.name
WHEN 'numeric' THEN c.prec
WHEN 'decimal' THEN c.prec
ELSE NULL
END AS [Precision],
CASE t.name
WHEN 'numeric' THEN c.scale
WHEN 'decimal' THEN c.scale
ELSE NULL
END AS Scale,
c.isnullable,
LEFT(o.name,30) AS TableName, 
d.name AS ConstraintName, 
cm.text AS DefaultValue, 
g1a.groupname,
c.collation,
CASE 
WHEN c.autoval IS NULL THEN 0
ELSE 1
END AS IdentityColumn
FROM syscolumns c 
INNER JOIN sysobjects o ON c.id = o.id
LEFT JOIN systypes t ON t.xusertype = c.xusertype --the first three joins get column names, data types, and column nullability.
LEFT JOIN sysobjects d ON c.cdefault = d.id --this left join gets column default constraint names.
LEFT JOIN syscomments cm ON cm.id = d.id --this left join gets default values for default constraints.
LEFT JOIN sysindexes g1 ON g1.id = o.id --the left join for sysfilegroups and sysindexes with aliases g1 and g1a
LEFT JOIN sysfilegroups g1a ON g1.groupid = g1a.groupid --are for determining which file group the table is in.
WHERE o.name = @TableName
AND g1.id = o.id AND g1.indid in (0, 1)  --these two conditions are to isolate the file group of the table.

END
--End filling temp table #TableScript

--Begin building create table and default value constraints scripts.
IF @NoTable = 0 AND @PKOnly = 0
BEGIN

SET @Query = 'if exists (select * from sysobjects where id = object_id(N' + '''[dbo].[' 
+ @TableName + @TableNameExt + ']''' + ') and OBJECTPROPERTY(id, N' + '''IsUserTable''' + ') = 1)'
+ CHAR(10) + 'drop table [dbo].[' + @TableName + @TableNameExt + ']'
+ CHAR(10) + 'GO'
+ CHAR(10) + CHAR(10) + 'CREATE TABLE [dbo].[' + @TableName + @TableNameExt + '] ('

DECLARE @DataType varchar(40),
@Length varchar(4),
@Precision varchar(4),
@Scale varchar(4),
@Isnullable varchar(1),
@DefaultValue varchar(255),
@GroupName varchar(35),
@ColumnName varchar(255),
@ConstraintName varchar(255),
@collation sysname,
@TEXTIMAGE_ON bit,
@IdentityColumn bit

SET @TEXTIMAGE_ON = 0

DECLARE ColumnName Cursor For
SELECT ColumnName
FROM #TableScript

OPEN ColumnName

FETCH NEXT FROM ColumnName INTO @ColumnName

WHILE (@@fetch_status = 0)
BEGIN
SELECT  @DataType = DataType, 
@Length = Length,
@Precision = [Precision],
@Scale = Scale, 
@Isnullable = isnullable,
@DefaultValue = DefaultValue,
@ConstraintName = ConstraintName,
@collation = collation,
@IdentityColumn = IdentityColumn
FROM #TableScript
WHERE ColumnName = @ColumnName

IF @DefaultValue IS NOT NULL
BEGIN

IF @DFQuery = ''
SET @DFQuery = @DFQuery 
+ CHAR(10) + CHAR(10) + 'ALTER TABLE [dbo].[' + @TableName + @TableNameExt + '] WITH NOCHECK ADD'

SET @DFQuery = @DFQuery 
+ CHAR(10) + CHAR(9) + 'CONSTRAINT [DF_' + @TableName + @TableNameExt + '_' 
+ @ColumnName + '_' + @DateTime + '] DEFAULT ' + @DefaultValue 
+ ' FOR [' + @ColumnName + '],'

END

IF @DataType = 'text' OR @DataType = 'ntext'
SET @TEXTIMAGE_ON = 1

SET @Query = @Query 
+ CHAR(10) + CHAR(9) + '[' + @ColumnName + '] [' + @DataType + ']'

IF @IdentityColumn = 1
SET @Query = @Query
+ ' IDENTITY (' + LTRIM(STR(IDENT_SEED(@TableName))) + ', ' + LTRIM(STR(IDENT_INCR(@TableName))) + ')'

IF @DataType = 'varchar' OR @DataType = 'nvarchar' OR @DataType = 'char' OR @DataType = 'nchar'
   OR @DataType = 'varbinary' OR @DataType = 'binary'
SET @Query = @Query 
+ ' (' + @Length + ')'

IF @DataType = 'numeric' OR @DataType = 'decimal'
SET @Query = @Query 
+ ' (' + @Precision + ', ' + @Scale + ')'

IF @collation IS NOT NULL AND @DataType <> 'sysname' AND @DataType <> 'ProperName'
SET @Query = @Query
+ ' COLLATE ' + @collation

IF @Isnullable = '1'
SET @Query = @Query + ' NULL'
ELSE
SET @Query = @Query + ' NOT NULL'

FETCH NEXT FROM ColumnName INTO @ColumnName
 
IF @@fetch_status = 0
SET @Query = @Query + ', '
END

CLOSE ColumnName
DEALLOCATE ColumnName

SET @Query = @Query 
+ CHAR(10) + ')'

--Assign file group name
SELECT DISTINCT @GroupName = GroupName
FROM #TableScript

IF @GroupName IS NOT NULL
SET @Query = @Query 
+ ' ON [' + @GroupName + ']'

IF @TEXTIMAGE_ON = 1
SET @Query = @Query 
+ ' TEXTIMAGE_ON [' + @GroupName + ']'

IF RIGHT(@DFQuery,1) = ','
SET @DFQuery = LEFT(@DFQuery, LEN(@DFQuery) - 1)

SET @Query = @Query 
+ CHAR(10) + 'GO' 

END

--End building create table and default value constraints scripts.

--Begin filling temp table #IndexScript.
INSERT INTO #IndexScript (IndexName, IndId, ColumnName, IndKey, UniqueIndex)
SELECT i.name, 
i.indid, 
c.name, 
k.keyno,
(i.status & 2)  --Learned this will identify a unique index from sp_helpindex
FROM sysindexes i 
INNER JOIN sysobjects o ON i.id = o.id
INNER JOIN sysindexkeys k ON i.id = k.id AND i.indid = k.indid
INNER JOIN syscolumns c ON c.id = k.id AND k.colid = c.colid
WHERE o.name = @TableName
AND i.indid > 0 and i.indid < 255 --eliminates non indexes
AND LEFT(i.name,7) <> '_WA_Sys'  --eliminates statistic indexes
--End filling temp table #IndexScript.

DECLARE @PK varchar(2),
@IndID int,
@IndexName varchar(255),
@IndKey int

SET @PK = ''
SET @IndKey = 1

SELECT DISTINCT @IndexName = IndexName, 
@IndID = indid
FROM #IndexScript
WHERE LEFT (IndexName, 2) = 'PK'

--Begin creating primary key script.
IF @PKOnly = 1 OR (@NoTable = 1 AND @NoPK = 0)
BEGIN
SET @Query = '--Add Primary Key' + CHAR(10)
SET @PK = 'PK'
END

IF @NoPK = 0
BEGIN

IF @IndexName IS NOT NULL
BEGIN

SET @Query = @Query 
+ CHAR(10) + CHAR(10) + 'ALTER TABLE [dbo].[' + @TableName + @TableNameExt + '] WITH NOCHECK ADD'
+ CHAR(10) + 'CONSTRAINT [PK_' + @TableName + @TableNameExt + @PK + '_' + @DateTime + '] PRIMARY KEY  '

IF @IndID = 1
SET @Query = @Query
+ 'CLUSTERED'
ELSE
SET @Query = @Query
+ 'NONCLUSTERED'


SET @Query = @Query
+ CHAR(10) + '('

DECLARE @OldColumnName varchar(255)

SET @OldColumnName = 'none_yet'

WHILE @IndKey <= 16
BEGIN
SELECT @ColumnName = ColumnName
FROM #IndexScript
WHERE IndexName = @IndexName AND IndID = @IndID AND IndKey = @IndKey

IF @ColumnName IS NOT NULL AND @ColumnName <> @OldColumnName
BEGIN
SET @Query = @Query 
+ CHAR(10) + '[' + @ColumnName + '],'
END

SET @OldColumnName = @ColumnName
SET @IndKey = @IndKey + 1 
END

IF RIGHT(@Query,1) = ','
SET @Query = LEFT(@Query, LEN(@Query) - 1)

SET @Query = @Query 
+ CHAR(10) + ')'

--Add file group name
IF @GroupName is not null
SET @Query = @Query 
+ ' ON [' + @GroupName + ']'

SET @Query = @Query 
+ CHAR(10) + 'GO'
END
END
--End creating primary key script.

--Add default value constraint script to main script.
IF @NoTable = 0 AND @PKOnly = 0
SET @Query = @Query 
+ @DFQuery
+ CHAR(10) + 'GO'

--Begin building index script.
IF @NoIndexes = 0 AND @PKOnly = 0
BEGIN

IF @NoPK = 0
SET @Query = @Query 
+ CHAR(10)

IF @NoTable = 1
SET @Query = @Query 
+ '--Add Indexes' + CHAR(10)
ELSE
SET @Query = @Query
+ CHAR(10)

DECLARE @IndexNameOrig varchar(255), 
@UniqueIndex int

DECLARE IndexName Cursor For
SELECT DISTINCT IndexName, 
indid,
UniqueIndex
FROM #IndexScript
WHERE LEFT (IndexName, 2) <> 'PK' AND LEFT(IndexName, 4) <> 'hind'

OPEN IndexName

FETCH NEXT FROM IndexName INTO @IndexName, @IndID, @UniqueIndex

WHILE @@fetch_status = 0
BEGIN
SET @IndexNameOrig = @IndexName

IF RIGHT(@IndexName,2) = 'PM' OR RIGHT(@IndexName,2) = 'AM'
SET @IndexName = LEFT(@IndexName, LEN(@IndexName) - 5)

IF LEFT(RIGHT(@IndexName,10),1) = '_'
SET @IndexName = LEFT(@IndexName, LEN(@IndexName) - 10)
ELSE
IF LEFT(RIGHT(@IndexName,11),1) = '_'
SET @IndexName = LEFT(@IndexName, LEN(@IndexName) - 11)
ELSE
IF LEFT(RIGHT(@IndexName,12),1) = '_'
SET @IndexName = LEFT(@IndexName, LEN(@IndexName) - 12)

SET @Query = @Query 
+ CHAR(10) + 'CREATE '

IF @IndID = 1
SET @Query = @Query
+ 'CLUSTERED '

IF @UniqueIndex <> 0
SET @Query = @Query
+ 'UNIQUE '

SET @Query = @Query 
+ 'INDEX [' + @IndexName + '_' + @DateTime + '] ON [dbo].[' + @TableName + @TableNameExt + ']('

SET @IndKey = 1
SET @OldColumnName = 'none_yet'

WHILE @IndKey <= 16
BEGIN
SELECT @ColumnName = ColumnName
FROM #IndexScript
WHERE IndexName = @IndexNameOrig AND IndID = @IndID AND IndKey = @IndKey

IF @ColumnName IS NOT NULL AND @ColumnName <> @OldColumnName
BEGIN
SET @Query = @Query 
+ '[' + @ColumnName + '],'
END

SET @OldColumnName = @ColumnName
SET @IndKey = @IndKey + 1 
END

IF RIGHT(@Query,1) = ','
SET @Query = LEFT(@Query, LEN(@Query) - 1)

SET @Query = @Query + ')'

--Add file group name
IF @GroupName is not null
SET @Query = @Query 
+ ' ON [' + @GroupName + ']'

SET @Query = @Query 
+ CHAR(10) + 'GO' + CHAR(10)

FETCH NEXT FROM IndexName INTO @IndexName, @IndID, @UniqueIndex
END

CLOSE IndexName
DEALLOCATE IndexName

END
--End building index script.

DROP TABLE #IndexScript

IF @NoTable = 0 AND @PKOnly = 0
DROP TABLE #TableScript

IF @DisplayScript = 1
PRINT @Query

IF @Exec = 1
BEGIN
--This code needed to remark out all GO commands before executing the code in the variable @Query
SET @Query = REPLACE(@Query,CHAR(10) + 'GO', CHAR(10) + '--GO')

Exec (@Query)
END

RETURN 0




GO