Print ‘Now Creating the Constraints (Primary Key, Unique, Foreign Key and Check Constraints…’
SET NOCOUNT ON
DECLARE
@sqlStatement NVARCHAR(1000),
@tmpSQL NVARCHAR(1000),
@table_name VARCHAR(40),
@index_name VARCHAR(40),
@r_cons_name VARCHAR(40),
@column_name VARCHAR(40),
@ind_typ VARCHAR(2),
@is_unique VARCHAR(1),
@CheckCons VARCHAR(2000),
@r_table_name VARCHAR(40),
@delete_rule VARCHAR(10),
@error INT
DECLARE @tmpTable TABLE (
table_name VARCHAR(30),
Cons_name VARCHAR(30),
column_name VARCHAR(30),
column_position TINYINT)
DECLARE cur_tab_ind CURSOR LOCAL READ_ONLY FOR
SELECT table_name, constraint_name , ind_typ, is_unique
FROM OPENQUERY (ORALINKSERVER,
‘SELECT table_name, constraint_name , constraint_type ind_typ, ”U” is_unique, ”N” Check1
FROM user_constraints
WHERE constraint_type IN (”P”,”U”)
UNION
SELECT table_name, index_name,”N”,”N”, ”Y”
FROM user_indexes
WHERE uniqueness <> ”UNIQUE” ORDER BY 1,3 desc’) a, sysobjects b
where table_name = UPPER(OBJECT_NAME(id))
order by 1,3 desc
INSERT INTO @tmpTable
SELECT * FROM OPENQUERY (ORALINKSERVER,
‘ SELECT i.table_name,i.index_name, column_name,column_position
FROM user_ind_columns c, user_indexes i
WHERE i.index_name = c.index_name
and i.table_name = c.table_name
and uniqueness <> ”UNIQUE”
union
SELECT uc.table_name,uc.constraint_name, column_name,ucc.position
FROM user_cons_columns ucc, user_constraints uc
WHERE uc.table_name = ucc.table_name
and uc.constraint_name = ucc.constraint_name
and constraint_type IN (”P”,”U”)
ORDER BY 1,2,4′)
OPEN cur_tab_ind
WHILE 1 < 2
BEGIN
FETCH NEXT FROM cur_tab_ind INTO
@table_name, @index_name, @ind_typ, @is_unique
IF @@FETCH_STATUS <> 0
BREAK
SET @sqlStatement = ‘ ‘
SET @tmpSQL = NULL
IF @ind_typ = ‘P’
BEGIN
IF @table_name = @index_name
SET @sqlStatement = ‘ALTER TABLE [‘+@table_name +’] WITH NOCHECK ADD CONSTRAINT [PK_’+@index_name+’] PRIMARY KEY CLUSTERED (‘
ELSE
SET @sqlStatement = ‘ALTER TABLE [‘+@table_name +’] WITH NOCHECK ADD CONSTRAINT [‘+@index_name+’] PRIMARY KEY CLUSTERED (‘
END
ELSE
BEGIN
IF @is_unique = ‘N’
SET @sqlStatement = ‘CREATE NONCLUSTERED INDEX ‘
ELSE
SET @sqlStatement = ‘CREATE UNIQUE NONCLUSTERED INDEX ‘
IF @table_name = @index_name
SET @sqlStatement = @sqlStatement + ‘UN_’ + @index_name + ‘ ON [‘ + @table_name+’](‘
ELSE
SET @sqlStatement = @sqlStatement + @index_name + ‘ ON [‘ + @table_name+’](‘
END
SELECT @tmpSQL = COALESCE(@tmpSQL+’,’,”)+ ‘[‘+column_name+’]’
FROM @tmpTable
WHERE table_name = @table_name
AND cons_name = @index_name
ORDER BY column_position
IF @ind_typ = ‘U’
SET @sqlStatement = @sqlStatement + @tmpSQL+’)’
ELSE
SET @sqlStatement = @sqlStatement + @tmpSQL+’) ON [PRIMARY]’
–Print @sqlStatement
EXEC sp_executeSQL @sqlstatement
END
Print ”
Print ‘Now Creating the Check constraints….’
Print ”
DECLARE cur_Check_cons CURSOR LOCAL READ_ONLY FOR
SELECT ‘ALTER table [‘+ a.table_name + ‘] WITH NOCHECK ADD Constraint [‘+
constraint_name+’] ‘+ ‘ CHECK (‘ ,a.search_condition FROM OPENQUERY (ORALINKSERVER,
‘SELECT k.table_name, k.constraint_name,k.search_condition,c.column_name
FROM user_constraints k, user_cons_columns c
WHERE k.constraint_name = c.constraint_name
and k.table_name = c.table_name
and k.constraint_type = ”C” ‘) a, sysobjects
WHERE a.table_name = OBJECT_NAME(id)
AND a.search_condition NOT LIKE ‘%IS NOT NULL’
OPEN cur_check_cons
WHILE 1 < 2
BEGIN
FETCH NEXT FROM cur_Check_Cons INTO @sqlStatement, @CheckCons
IF @@FETCH_STATUS <> 0
BREAK
SET @sqlStatement = @sqlStatement+ @CheckCons + ‘)’
Print @sqlStatement
EXEC sp_executeSQL @sqlStatement
END
Print ”
Print ‘Now Creating Foreign Key constraints….’
Print ”
DECLARE cur_Fkeys CURSOR LOCAL READ_ONLY FOR
SELECT ‘ALTER TABLE [‘+table_name +’] Add Constraint [‘+ constraint_name +’] FOREIGN KEY (‘,
table_name, constraint_name, r_constraint_name, r_table_name, delete_rule
FROM OPENQUERY (ORALINKSERVER,
‘SELECT k.table_name,k.constraint_name,k.r_constraint_name, r.table_name r_table_name,
k.delete_rule
FROM user_constraints k, user_constraints r
WHERE k.r_constraint_name = r.constraint_name
and k.constraint_type = ”R”
and r.constraint_type IN (”P”,”U”) ORDER BY 1′)a, sysobjects
WHERE a.table_name = OBJECT_NAME(id)
DELETE FROM @tmpTable
INSERT INTO @tmpTable
SELECT table_name, constraint_name, column_name, position
FROM OPENQUERY (ORALINKSERVER,
‘ SELECT c.table_name, c.constraint_name,c.column_name,c.position
FROM user_cons_columns c, user_constraints k
where c.constraint_name = k.constraint_name
and c.table_name = k.table_name
and k.constraint_type IN (”P”,”U”,”R”) ‘)
OPEN cur_Fkeys
WHILE 1 < 2
BEGIN
SET @sqlStatement = ”
FETCH NEXT FROM cur_Fkeys INTO
@sqlStatement, @table_name, @index_name, @r_cons_name, @r_table_name, @delete_rule
IF @@FETCH_STATUS <> 0
BREAK
SET @CheckCons = NULL
SELECT @CheckCons = COALESCE(@CheckCons+’,’,”)+ ‘[‘+column_name+’]’
FROM @tmpTable
WHERE table_name = @table_name
AND cons_name = @index_name
ORDER BY column_position
SET @sqlStatement = @sqlStatement + @CheckCons+’) REFERENCES [‘+ @r_table_name + ‘](‘
SET @CheckCons = null
SELECT @CheckCons = COALESCE(@CheckCons+’,’,”)+ ‘[‘+column_name+’]’
FROM @tmpTable
WHERE table_name = @r_table_name
AND cons_name = @r_cons_name
ORDER BY column_position
SET @sqlStatement = @sqlStatement + @CheckCons+’) on Delete ‘+@delete_rule
–Print @sqlStatement
EXEC sp_executeSQL @sqlstatement
END
DELETE FROM @tmpTable
Print ‘Foreign Key Constraints Created…..’
Print ‘End of Migration process…..’
]]>