Robert L. Stewart
rl_stewart at highstream.net
Thu May 10 11:16:32 CDT 2007
John,
Here is the first installment.
These scripts will create the 2 tables and 1 view that the
stored procedure uses to create a table.
More later
******** Watch for line wrap
CREATE DEFAULT dbo.Default_0 as 0
GO
CREATE DEFAULT dbo.Default_Now as GETDATE()
GO
/****** Object: Table [dbo].[tsysImportDefinitionColumns] Script
Date: 05/10/2007 11:15:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tsysImportDefinitionColumns](
[ImportDefColumnID] [int] IDENTITY(1,1) NOT NULL,
[ImportDefID] [int] NOT NULL,
[ColumnName] [varchar](64) NOT NULL,
[ColumnStart] [int] NOT NULL,
[ColumnEnd] [int] NOT NULL,
[ColumnDataType] [varchar](15) NOT NULL,
[ColumnLength] [varchar](10) NULL,
[ColumnPrecision] [tinyint] NULL,
[ColumnScale] [tinyint] NULL,
CONSTRAINT [PK_tsysImportDefinitionColumns] PRIMARY KEY CLUSTERED
(
[ImportDefColumnID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
/****** Object: Table [dbo].[tsysImportDefinition] Script Date:
05/10/2007 11:15:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tsysImportDefinition](
[ImportDefID] [int] IDENTITY(1,1) NOT NULL,
[ImportDefName] [varchar](50) NOT NULL,
[ImportDefDesc] [varchar](500) NULL,
[DBName] [varchar](64) NOT NULL,
[DBTableName] [varchar](64) NOT NULL,
[ArchivedFlag] [bit] NOT NULL,
[AuditArchiveDate] [datetime] NULL,
[AuditCreateDate] [datetime] NOT NULL,
[AuditModifyDate] [datetime] NULL,
CONSTRAINT [PK_tsysImportDefinition] PRIMARY KEY CLUSTERED
(
[ImportDefID] ASC
) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[Default_0]',
@objname=N'[dbo].[tsysImportDefinition].[ArchivedFlag]' ,
@futureonly='futureonly'
GO
EXEC sys.sp_bindefault @defname=N'[dbo].[Default_Now]',
@objname=N'[dbo].[tsysImportDefinition].[AuditCreateDate]' ,
@futureonly='futureonly'
GO
/****** Object: View [dbo].[vwImportDefColumns] Script Date:
05/10/2007 11:15:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwImportDefColumns]
AS
SELECT dbo.tsysImportDefinition.DBName,
dbo.tsysImportDefinition.DBTableName, dbo.tsysImportDefinition.ArchivedFlag,
dbo.tsysImportDefinitionColumns.ColumnName,
dbo.tsysImportDefinitionColumns.ColumnStart,
dbo.tsysImportDefinitionColumns.ColumnEnd,
dbo.tsysImportDefinitionColumns.ColumnDataType,
dbo.tsysImportDefinitionColumns.ColumnLength,
dbo.tsysImportDefinitionColumns.ColumnPrecision,
dbo.tsysImportDefinitionColumns.ColumnScale
FROM dbo.tsysImportDefinition INNER JOIN
dbo.tsysImportDefinitionColumns ON
dbo.tsysImportDefinition.ImportDefID =
dbo.tsysImportDefinitionColumns.ImportDefID
WHERE (dbo.tsysImportDefinition.ArchivedFlag = 0)
GO
CREATE PROCEDURE [dbo].[pImportCountryFile]
AS
BEGIN
SET NOCOUNT ON;
-- declare the variables you will need for the country columns
DECLARE @CountryCode VARCHAR(3),
@CountryName VARCHAR(100),
@ColumnName VARCHAR(64),
@ColumnStart INT,
@ColumnEnd INT,
@ColumnDataType VARCHAR(15),
@ColumnLength VARCHAR(10),
-- column length defined as varchar to take MAX as a parameter
@ColumnPrecision TINYINT,
@ColumnScale TINYINT,
@DBName VARCHAR(64),
@DBTableName VARCHAR(64)
-- Declare the variable you will ned for your dynamic SQL statements
DECLARE @CreateTable NVARCHAR(4000)
-- the following code is generic for creating the table
-- the only thing that would be changed is being able to
-- pass into the proc the name of the table you want to
-- do the build of. for your system, it would probably be
-- the client job information
DECLARE cColumns CURSOR
FOR
SELECT DBName,
DBTableName,
ColumnName,
ColumnStart,
ColumnEnd,
ColumnDataType,
ColumnLength,
ColumnPrecision,
ColumnScale
FROM dbo.vwImportDefColumns
WHERE DBTableName = 'tlkpCountry'
OPEN cColumns
FETCH NEXT FROM cColumns
INTO @DBName,
@DBTableName,
@ColumnName,
@ColumnStart,
@ColumnEnd,
@ColumnDataType,
@ColumnLength,
@ColumnPrecision,
@ColumnScale
-- build the create table sql
SET @CreateTable = 'CREATE TABLE dbo.' + @DBTableName + ' ('
+ 'PK_ID int identity(1,1), '
WHILE (@@FETCH_STATUS = 0) -- There are records
BEGIN
-- build the create table sql
IF (CHARINDEX('varchar', at ColumnDataType,1) > 0)
BEGIN
SET @CreateTable = @CreateTable + @ColumnName
+ ' ' + @ColumnDataType + '(' +
+ @ColumnLength + '), '
END
ELSE IF (CHARINDEX('int', at ColumnDataType,1) > 0)
BEGIN
SET @CreateTable = @CreateTable + @ColumnName
+ ' ' + @ColumnDataType + ', '
END
ELSE IF (CHARINDEX('date', at ColumnDataType,1) > 0)
BEGIN
SET @CreateTable = @CreateTable + @ColumnName
+ ' ' + @ColumnDataType + ', '
END
ELSE IF (CHARINDEX('text', at ColumnDataType,1) > 0)
BEGIN
SET @CreateTable = @CreateTable + @ColumnName
+ ' ' + @ColumnDataType + ', '
END
ELSE -- money, numeric or decimal
BEGIN
SET @CreateTable = @CreateTable + @ColumnName
+ ' ' + @ColumnDataType + '(' +
+ CAST(@ColumnPrecision AS varchar(10))
+ CAST(@ColumnScale AS varchar(10)) + '), '
END
FETCH NEXT FROM cColumns
INTO @DBName,
@DBTableName,
@ColumnName,
@ColumnStart,
@ColumnEnd,
@ColumnDataType,
@ColumnLength,
@ColumnPrecision,
@ColumnScale
END
-- trim the final , from the end of the string
SET @CreateTable = substring(@CreateTable,1,len(@CreateTable) - 1) + ')'
PRINT @CreateTable
-- Execute the create table statement
-- EXEC sp_ExecuteSql @CreateTable
CLOSE cColumns
DEALLOCATE cColumns
END
GO