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