Sign up ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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:


For each source table{
 While (Destination Tbl =<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.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

I think you are over complicating your requirement.

Your best option is to

  1. Create a backup of PROD on a test server
  2. drop all the FK's
  3. migrate the data you want to destination server using SSIS or T-SQL or BCP (which ever one you prefer).
  4. 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 as from_schema
        ,       o1.Name as from_table
        , 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 ( = 
                    and =

    ,ordered_tables AS (
        SELECT as schemaName, as tableName, 0 AS Level
        FROM    (   select  *
                    from    $(db).sys.tables 
                    where   name <> 'sysdiagrams') t
        INNER   JOIN $(db).sys.schemas s
        on      t.schema_id = s.schema_id
        LEFT    OUTER JOIN fk_tables fk
        ON = fk.from_schema
        AND = 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;

    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

        WHILE   @@FETCH_STATUS = 0
                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;
        IF @@TRANCOUNT > 0
            ROLLBACK TRAN;

    IF @@TRANCOUNT > 0
        COMMIT TRAN;

    IF CURSOR_STATUS('global','tables_cursor') >= 0
        CLOSE tables_cursor;
        DEALLOCATE tables_cursor;
share|improve this answer
Thank you, Kin! The linked article is essentially spot on to my dilemma. – SRTR4K Sep 2 '13 at 7:38

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.