SQL Server 2008 - How to check FK integrity? I'd like to pull a partial dataset from production for test environments - e.g. Copy the production database with only the first 100 rows of each table or 5% of the data - which ever comes first. Simple enough. For each table, just... SELECT TOP 100 * INTO DESTINATION FROM SOURCE" 

The problem is maintaining data integrity for FK. Is there a native SQL Server SP/FX to verify the integrity of each row's FK after the data has been copied over?

If not, once the production schema is copied to sandbox environments, I was planning to implement the following logic:

PSUEDO:

For each source table{
 While (Destination Tbl =&lt;100 rows/5% of Source Tbl COUNT(*)) {
   If FK exists on source tbl{
     For each FK, query referenced FK tbl and insert results into FK's corresponding destination tbl
   }
  Insert row from source tbl to destination tbl
 }
}


I don't want to recreate the wheel or make it harder than it needs to be. If you see any holes in my logic, please let me know. Thanks in advance.
I think you are over complicating your requirement. 

Your best option is to 


Create a backup of PROD on a test server
drop all the FK's 
migrate the data you want to destination server using SSIS or T-SQL or BCP (which ever one you prefer).
create FK's back.


In SQL Server you don't have a tool to do it out of the box. You will end up writing more complex tsql code which I doubt will achieve what you are trying to do + you have to take care of Identity columns as well.

Below script would loop over all the tables and select them one-by-one by taking into account referential integrity constraints (i.e. foreign keys). 

Note: Below script is borrowed and modified from here written by Jamie Thomson.

    -- make sure to use SQLCMD mode in SSMS and change the database Name.
    :setvar db Adventureworks2008


    DECLARE @tables TABLE (
        schemaName sysname
    ,   tableName sysname
    ,   level int
    );

    with fk_tables as (
        select  s1.name as from_schema
        ,       o1.Name as from_table
        ,       s2.name as to_schema
        ,       o2.Name as to_table
        from    $(db).sys.foreign_keys fk
        inner   join $(db).sys.objects o1
        on      fk.parent_object_id = o1.object_id
        inner   join $(db).sys.schemas s1
        on      o1.schema_id = s1.schema_id
        inner   join $(db).sys.objects o2
        on      fk.referenced_object_id = o2.object_id
        inner   join $(db).sys.schemas s2
        on      o2.schema_id = s2.schema_id
        --For the purposes of finding dependency hierarchy 
        --we're not worried about self-referencing tables
        where   not (   s1.name = s2.name 
                    and o1.name = o2.name)
    )

    ,ordered_tables AS (
        SELECT  s.name as schemaName, t.name as tableName, 0 AS Level
        FROM    (   select  *
                    from    $(db).sys.tables 
                    where   name &lt;&gt; 'sysdiagrams') t
        INNER   JOIN $(db).sys.schemas s
        on      t.schema_id = s.schema_id
        LEFT    OUTER JOIN fk_tables fk
        ON      s.name = fk.from_schema
        AND     t.name = fk.from_table
        WHERE   fk.from_schema IS NULL
        UNION   ALL
        SELECT  fk.from_schema, fk.from_table, ot.Level + 1
        FROM    fk_tables fk
        INNER   JOIN ordered_tables ot
        ON      fk.to_schema = ot.schemaName
        AND     fk.to_table = ot.tableName
    )
    insert  @tables
    select  distinct ot.schemaName,ot.tableName,ot.Level
    from    ordered_tables ot
    inner   join (
            select  schemaName,tableName,MAX(Level) maxLevel
            from    ordered_tables
            group   by schemaName,tableName
    ) mx
    on ot.schemaName = mx.schemaName
    and ot.tableName = mx.tableName
    and mx.maxLevel = ot.Level
    ORDER   BY [Level] DESC;

    DECLARE tables_cursor CURSOR FORWARD_ONLY
    FOR
    SELECT  schemaName, tableName
    FROM    @tables
    ORDER   BY [Level] DESC;

    DECLARE @schemaName         sysname
    ,       @tableName          sysname
    ,       @schemaTableName    NVARCHAR(MAX);

    DECLARE @vSQL nvarchar(max);
    OPEN    tables_cursor;
    FETCH   NEXT FROM tables_cursor INTO @schemaName, @tableName

    BEGIN TRAN
    BEGIN TRY
        WHILE   @@FETCH_STATUS = 0
        BEGIN
                SET @schemaTableName = '[' + @schemaName + '].[' + @tableName + ']';
                SET @vSQL = 'select top(100) * FROM $(db).' + @schemaTableName;
                exec (@vSQL);
                PRINT 'Selected ' + CAST(@@ROWCOUNT AS VARCHAR) + ' rows from $(db).' + @schemaTableName;
                FETCH NEXT FROM tables_cursor INTO @schemaName, @tableName;
        END
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT &gt; 0
            ROLLBACK TRAN;
        PRINT ERROR_MESSAGE();
    END CATCH

    IF @@TRANCOUNT &gt; 0
        COMMIT TRAN;

    IF CURSOR_STATUS('global','tables_cursor') &gt;= 0
    BEGIN
        CLOSE tables_cursor;
        DEALLOCATE tables_cursor;
    END
    GO