So, we've gotten into the business of sanitizing or scrambling sensitive production data for development environments. This is, for the most part, was the direction to do this for mainframe flat files. Now comes along distributed environments, mostly 3rd party vendor applications on sql server. You should see some of the twisted things these apps do. Using reserved words as column names, creating tables with the same name but different owners..the list is long.
In any case, when we told the people who support this mess that we would need fixed width flat files, they were.."but...but...we have over 1,000 tables". Long story short, I wrote a bunch of sprocs to automate alot of the steps needed to get the data out and put it back so they could have a sanitized environment.
One of the issues we ran across was how to handle all of this with RI. Now I guess I could have had them set up a data dictionary that show the relationships, but I choose a lazy way out. I used the catalogs to copy all the RI to a Work table, DROP All of the RI (This is done with another sproc I have yet o post) so that it could be "replayed" after I was done with the TRUNCATES and the bcp in's. Here's a sproc that will log all the RI for any database. Be forewarned...it takes dynamic sql to a whole other level. If anyone has anything simpler, I'd like to see it, but this works fine.
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_Gen_FK_code]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_Gen_FK_code]
GO
CREATE procedure isp_Gen_FK_code
@dbname varchar(255)
AS
/*
EXEC isp_Gen_FK_code
@dbname = 'OHM_Prod'
SELECT * FROM FK_create_code
*/
SET NOCOUNT ON
DECLARE @tablename nvarchar(128), @column nvarchar(128), @schema nvarchar(128), @constraint nvarchar(128)
DECLARE @fktable nvarchar(128), @fkconstraint nvarchar(128), @onupdate varchar(9), @ondelete varchar(9)
DECLARE @comma char(1), @createsql nvarchar(4000), @dropsql nvarchar(4000), @truncatesql nvarchar(4000)
DECLARE @DYSQL nvarchar(4000),@ColList sysname, @ColList2 sysname
DECLARE @error_out int, @Result_Count int, @Error_Message varchar(255), @Error_Type int, @Error_Loc int, @rc int
SELECT @rc = 0
CREATE TABLE ##Key_Column_usage(Constraint_catalog sysname,CONSTRAINT_SCHEMA sysname, CONSTRAINT_NAME varchar(300)
,TABLE_CATALOG sysname, TABLE_SCHEMA sysname, TABLE_NAME sysname, COLUMN_NAME sysname
,ORDINAL_POSITION int)
SET @DYSQL = 'use '+@dbname+'
insert into ##Key_Column_usage
select '''+@dbname+''' as CONSTRAINT_CATALOG
,user_name(c_obj.uid) as CONSTRAINT_SCHEMA
,c_obj.name as CONSTRAINT_NAME
,'''+@dbname+''' as TABLE_CATALOG
,user_name(t_obj.uid) as TABLE_SCHEMA
,t_obj.name as TABLE_NAME
,col.name as COLUMN_NAME
,case col.colid
when ref.fkey1 then 1
when ref.fkey2 then 2
when ref.fkey3 then 3
when ref.fkey4 then 4
when ref.fkey5 then 5
when ref.fkey6 then 6
when ref.fkey7 then 7
when ref.fkey8 then 8
when ref.fkey9 then 9
when ref.fkey10 then 10
when ref.fkey11 then 11
when ref.fkey12 then 12
when ref.fkey13 then 13
when ref.fkey14 then 14
when ref.fkey15 then 15
when ref.fkey16 then 16
end as ORDINAL_POSITION
from
'+@dbname+'.dbo.sysobjects c_obj
,'+@dbname+'.dbo.sysobjects t_obj
,'+@dbname+'.dbo.syscolumns col
,'+@dbname+'.dbo.sysreferences ref
where
permissions(t_obj.id) != 0
and c_obj.xtype in ('+'''F'''+ ')
and t_obj.id = c_obj.parent_obj
and t_obj.id = col.id
and col.colid in
(ref.fkey1,ref.fkey2,ref.fkey3,ref.fkey4,ref.fkey5,ref.fkey6,
ref.fkey7,ref.fkey8,ref.fkey9,ref.fkey10,ref.fkey11,ref.fkey12,
ref.fkey13,ref.fkey14,ref.fkey15,ref.fkey16)
and c_obj.id = ref.constid
union
select
'''+@dbname+''' as CONSTRAINT_CATALOG
,user_name(c_obj.uid) as CONSTRAINT_SCHEMA
,i.name as CONSTRAINT_NAME
,'''+@dbname+''' as TABLE_CATALOG
,user_name(t_obj.uid) as TABLE_SCHEMA
,t_obj.name as TABLE_NAME
,col.name as COLUMN_NAME
,v.number as ORDINAL_POSITION
from
'+@dbname+'.dbo.sysobjects c_obj
,'+@dbname+'.dbo.sysobjects t_obj
,'+@dbname+'.dbo.syscolumns col
,master.dbo.spt_values v
,'+@dbname+'.dbo.sysindexes i
where
permissions(t_obj.id) != 0
and c_obj.xtype in ('+'''UQ'''+' ,'+'''PK'''+')
and t_obj.id = c_obj.parent_obj
and t_obj.xtype = '+'''U'''+'
and t_obj.id = col.id
and col.name = index_col(user_name(t_obj.uid)+'+'''.'''+'+t_obj.name,i.indid,v.number)
and t_obj.id = i.id
and c_obj.name = i.name
and v.number > 0
and v.number <= i.keycnt
and v.type = '+'''P'''+''
execute sp_executesql @DYSQL
TRUNCATE TABLE FK_create_code
SET @DYSQL = 'declare cstrts cursor fast_forward read_only for
SELECT DISTINCT
c.[TABLE_SCHEMA]
, c.[TABLE_NAME]
, u.CONSTRAINT_NAME
FROM ' +@dbname+'.[INFORMATION_SCHEMA].[COLUMNS] c
JOIN ##Key_Column_usage u
ON c.[TABLE_NAME] = u.[TABLE_NAME]
AND c.[TABlE_SCHEMA] = u.[TABLE_SCHEMA]
AND c.[COLUMN_NAME] = u.[COLUMN_NAME]
JOIN ' +@dbname+'.[INFORMATION_SCHEMA].[table_constraints] t
ON u.[CONSTRAINT_NAME] = t.[CONSTRAINT_NAME]
WHERE t.[CONSTRAINT_TYPE] = ' + '''FOREIGN KEY'''
execute sp_executesql @DYSQL
Select @error_out = @@error
If @error_out <> 0
BEGIN
SELECT @Error_Loc = 1, @Error_Type = 50001, @rc = -1
GOTO isp_Gen_FK_code_Error
END
OPEN cstrts
fetch next from cstrts
into @schema, @tablename, @constraint
while @@fetch_status = 0
begin
SET @DYSQL = 'DECLARE @cr nchar(2), @go nvarchar(8)
SET @cr = nchar(13)+nchar(10)
SET @go = @cr + '+'''GO'''+' + @cr
SELECT DISTINCT
@fktable = u2.[TABLE_NAME]
, @fkconstraint = r.[UNIQUE_CONSTRAINT_NAME]
, @onupdate = r.[UPDATE_RULE]
, @ondelete = r.[DELETE_RULE]
--, @column = u.[COLUMN_NAME]
FROM ' +@dbname+'.[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] r
JOIN ##Key_Column_usage u2
ON r.[UNIQUE_CONSTRAINT_NAME] = u2.[CONSTRAINT_NAME]
JOIN ##Key_Column_usage u
ON u.[CONSTRAINT_NAME] = r.[CONSTRAINT_NAME]
WHERE r.[CONSTRAINT_NAME] = @constraint
SELECT @ColList = Null
select @ColList = COALESCE(@ColList + '+ ''','''+ ', '+'''''' +') + '
+'''['''+' +CAST(COLUMN_NAME AS sysname)+'+''']'''+'
FROM (SELECT DISTINCT TOP 100 u.COLUMN_NAME, u.[ORDINAL_POSITION]
FROM ' +@dbname+'.[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS] r
JOIN ##Key_Column_usage u2
ON r.[UNIQUE_CONSTRAINT_NAME] = u2.[CONSTRAINT_NAME]
JOIN ##Key_Column_usage u
ON u.[CONSTRAINT_NAME] = r.[CONSTRAINT_NAME]
WHERE r.[CONSTRAINT_NAME] = @constraint
ORDER BY u.[ORDINAL_POSITION]
) AS XXX
set @createsql =
'+ '''ALTER TABLE ['+@dbname+'].[''' +'
+ @schema
+ '+'''].['''+'
+ @tablename
+ '+'''] ADD CONSTRAINT ['''+'
+ @constraint
+ '+'''] '''+'
+ @cr
+ '+'''FOREIGN KEY ('''+'
+ @colList
+ '+''') REFERENCES ['+@dbname+'].[''' +'
+ @schema
+ '+'''].['''+'
+ @fktable
+ '+'''] ('''+'
CREATE TABLE #Ver(Dbversion varchar(2000))
INSERT INTO #Ver
SELECT @@Version
SELECT @ColList2 = Null
select @ColList2 = COALESCE(@ColList2 + '+ ''','''+ ', '+'''''' +') + '
+'''['''+' +CAST(c.COLUMN_NAME AS sysname)+'+''']'''+'
FROM ' +@dbname+'.[INFORMATION_SCHEMA].[COLUMNS] c
JOIN ##Key_Column_usage u
ON c.[TABLE_NAME] = u.[TABLE_NAME]
AND c.[COLUMN_NAME] = u.[COLUMN_NAME]
WHERE u.[CONSTRAINT_NAME] = @fkconstraint
AND u.[CONSTRAINT_SCHEMA] = c.[TABLE_SCHEMA]
ORDER BY u.[ORDINAL_POSITION]
set @createsql = @createsql + @colList2
IF EXISTS(SELECT * FROM #Ver where dbversion like '+ '''%8.00%'''+')
BEGIN
set @createsql = @createsql + '+''') ON DELETE '''+'
+ @ondelete
+ '+''' ON UPDATE '''+'
+ @onupdate
END
ELSE
BEGIN
set @createsql = @createsql + '+''')'''+'
END
INSERT INTO FK_Create_code (FK_Code)
VALUES (@createsql)
--print @createsql'
execute sp_executesql @DYSQL,N' @tablename nvarchar(128), @column nvarchar(128), @schema nvarchar(128), @constraint nvarchar(128)
,@fktable nvarchar(128), @fkconstraint nvarchar(128), @onupdate varchar(9), @ondelete varchar(9)
, @comma char(1), @createsql nvarchar(4000), @dropsql nvarchar(4000), @truncatesql nvarchar(4000),@ColList nvarchar(300)
, @colList2 nvarchar(300)'
,@tablename,@column,@schema, @constraint, @fktable, @fkconstraint , @onupdate, @ondelete, @comma, @createsql
, @dropsql, @truncatesql, @ColList, @colList2
Select @error_out = @@error
If @error_out <> 0
BEGIN
SELECT @Error_Loc = 1, @Error_Type = 50001, @rc = -1
GOTO isp_Gen_FK_code_Error
END
fetch next from cstrts
into @schema, @tablename, @constraint
end
isp_Gen_FK_code_Exit:
close cstrts
deallocate cstrts
DROP TABLE ##KEY_column_usage
RETURN @rc
SET NOCOUNT OFF
isp_Gen_FK_code_Error:
If @Error_Type = 50001
BEGIN
Select @error_message = (Select 'Location: ' + ',"' + RTrim(Convert(char(3),@Error_Loc))
+ ',"' + ' @@ERROR: ' + ',"' + RTrim(Convert(char(6),error))
+ ',"' + ' Severity: ' + ',"' + RTrim(Convert(char(3),severity))
+ ',"' + ' Message: ' + ',"' + RTrim(description)
From master..sysmessages
Where error = @error_out)
END
RAISERROR @Error_Type @Error_Message
GOTO isp_Gen_FK_code_Exit
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO