Scripts
— Script to Create Tables
BEGIN
SET NOCOUNT ON
DECLARE
@sqlStatement NVARCHAR(4000),
@tmpSQL NVARCHAR(4000),
@i INT,
@table_name VARCHAR(40),
@column_Name VARCHAR(40),
@data_default VARCHAR(250),
@seq_name VARCHAR(30),
@last_value INT,
@error INT,
@isMinCol BIT,
@data_type VARCHAR(50),
@data_type1 VARCHAR(50),
@isNullable CHAR(1),
@isMaxCol BIT,
@data_precision INT,
@data_scale INT,
@data_length INT
DECLARE cur_CreateTab CURSOR LOCAL READ_ONLY FOR
SELECT * FROM OPENQUERY (ORALINKSERVER,
‘SELECT cl.table_name,
DECODE(cl.column_id, (gr.min_col),1,0) isminCol, cl.column_name,
cl.data_type,
NVL(cl.data_length,0),
NVL(cl.data_precision,0),
NVL(cl.data_scale,0),
cl.nullable,
decode(cl.column_id, (max_col), 1,0 ) ismaxcol
FROM user_tab_columns cl ,
user_objects uo, (
SELECT table_name, min(column_id) min_col,
max(column_id) max_col
FROM user_tab_columns
GROUP BY table_name) gr
WHERE cl.table_name = gr.table_name
AND cl.table_name = uo.object_name
AND uo.object_type = ”TABLE” order by cl.table_name,cl.column_id’)
OPEN cur_CreateTab
WHILE 1 < 2
BEGIN
SET @table_name = ”
SET @isMinCol = null
SET @column_name = NULL
SET @data_type = NULL
SET @data_type1 = NULL
SET @isNullable = NULL
SET @isMaxCol = NULL
SET @data_precision = null
SET @data_scale = null
SET @data_length = null
FETCH NEXT FROM cur_CreateTab
INTO @table_name, @isMinCol, @column_name, @data_type,
@data_length, @data_precision, @data_scale, @isNullable,
@isMaxCol
IF @@FETCH_STATUS <> 0
BREAK
IF @isMinCol = 1
BEGIN
SET @sqlStatement = ‘ Create Table [‘ + @table_name +’](‘
— Print @sqlStatement
END
SET @sqlStatement = ISNULL(@sqlStatement,”) + ‘ [‘+@Column_name+’] ‘
SELECT @data_Type1 = CASE @data_type
WHEN ‘CHAR’ THEN ‘CHAR(‘+CAST(@data_length as VARCHAR)+’)’
WHEN ‘VARCHAR’ THEN ‘VARCHAR(‘+CAST(@data_length AS VARCHAR)+’)’
WHEN ‘VARCHAR2’ THEN ‘VARCHAR(‘+CAST(@data_length AS VARCHAR)+’)’
WHEN ‘LONG RAW’ THEN ‘IMAGE’
WHEN ‘LONG’ THEN ‘TEXT’
WHEN ‘DATE’ THEN ‘DATETIME’
WHEN ‘BLOB’ THEN ‘IMAGE’
WHEN ‘NUMBER’ THEN CASE @data_precision
WHEN 0 THEN ‘FLOAT’
ELSE ‘DECIMAL(‘+ CAST(@data_precision as VARCHAR)+’,’+CAST( @data_scale as VARCHAR)+’)’
END
WHEN ‘FLOAT’ THEN ‘FLOAT’
ELSE @data_type
END
SET @sqlStatement = @sqlStatement +’ ‘+ @data_type1
IF @isNullable = ‘N’
SET @sqlStatement = ISNULL(@sqlStatement,”) + ‘ NOT NULL ‘
IF @isMaxCol = 1
BEGIN
SET @sqlStatement = ISNULL(@sqlStatement,”) + ‘)’
EXEC sp_ExecuteSql @sqlStatement
SET @sqlStatement = ”
END
ELSE
SET @sqlStatement = ISNULL(@sqlStatement,”) + ‘,’
END
END
Print ‘Handling the Collation….’
BEGIN
DECLARE
@sqlStatement NVARCHAR(1000)
DECLARE Cur_Collate CURSOR LOCAL READ_ONLY FOR
SELECT ‘ALTER TABLE [‘+ object_name(a.id)+ ‘] ALTER COLUMN [‘+ a.name+ ‘] ‘
+c.name+
ISNULL(CASE c.name
WHEN ‘TEXT’ THEN NULL
WHEN ‘NTEXT’ THEN NULL
ELSE ‘(‘+CAST(a.length AS VARCHAR)+’)’
END,’ ‘)+ ‘ COLLATE SQL_Latin1_General_CP1_CS_AS ‘+ ISNULL(CASE isnullable
WHEN 0 THEN ‘ NOT NULL’
ELSE null
END, ”)
FROM syscolumns a, sysobjects b, systypes c
WHERE a.id = b.id
and user_name(b.uid) = ‘dbo’
and b.xtype = ‘U’
and a.xtype= c.xtype
and a.xtype IN (167,239,175,231)
Order by object_name(a.id)
OPEN Cur_Collate
WHILE 1 < 2
BEGIN
FETCH NEXT FROM cur_collate INTO @sqlStatement
IF @@FETCH_STATUS <> 0
BREAK
EXEC sp_executeSQL @sqlStatement
END
SET @sqlStatement = ”
END
–Handling Sequences
BEGIN
Print ”
Print ‘Now Creating the Sequence table…’
DECLARE
@seq_name VARCHAR(30),
@sqlStatement NVARCHAR(1000),
@last_value INT,
@error INT
DECLARE cur_seq CURSOR LOCAL READ_ONLY FOR
SELECT * FROM OPENQUERY(ORALINKSERVER,
‘SELECT sequence_name,last_number seq
FROM user_sequences’)
SET @sqlStatement = ‘ CREATE TABLE dbo.SEQUENCE_TAB (‘ + ‘seq_name varchar (30) NOT NULL , ‘
+’seq_value bigint NOT NULL ,’ +
‘CONSTRAINT XPK_SEQUENCE_TAB PRIMARY KEY ‘+’
CLUSTERED (seq_name ))’
EXEC sp_executeSQL @sqlStatement
OPEN cur_seq
WHILE 1 < 2
BEGIN
FETCH NEXT FROM cur_seq INTO @seq_name, @last_Value
IF @@FETCH_STATUS <> 0
BREAK
SET @sqlStatement = ‘INSERT INTO SEQUENCE_TAB VALUES(”’+@seq_name+”’,’ +CAST(@last_Value AS VARCHAR)+ ‘ )’
EXEC sp_executeSQL @sqlStatement
END
END
Print ‘Procedure to get next sequence value…’
CREATE PROCEDURE fnGetAutoID
@sTableName VARCHAR(40)
AS
BEGIN TRAN
SET NOCOUNT ON
DECLARE @sSeqNum VARCHAR(40)
IF @sTableName IS NULL
RETURN
ELSE
SELECT @sSeqNum = CAST(ISNULL(seq_value,0)+1 AS VARCHAR)
FROM Sequence_Tab WITH (HOLDLOCK ROWLOCK )
WHERE seq_name = UPPER(@sTableName)
IF @sSeqNum IS NOT NULL
UPDATE Sequence_Tab
SET seq_value = @sSeqNum
WHERE seq_name = UPPER(@sTableName)
IF @@error <> 0
RETURN NULL
COMMIT TRAN
RETURN CAST(@sSeqNum AS INT)
Print ‘Now Creating defaults…’