SQL Server Performance

Delete Data of all Tables with No. of Records left in Tables

Discussion in 'SQL Server 2005 General Developer Questions' started by sqlderby, Aug 2, 2010.

  1. sqlderby Member

    Hi All
    I have situation where only 10 records need to left in the production database for testing purpose. Data of 10 records should be relevant as per referential rule between tables. It is seems to be easier to delete (or truncate tables) with all of data as I am using following script. But need help to modify it as per above requirement.
    Script....
    SET NOCOUNT ON
    Declare @SQL VarChar(500)
    Declare @TableName VarChar(255)
    Declare @ConstraintName VarChar(500)
    Declare curAllForeignKeys SCROLL CurSor For Select Table_Name,Constraint_Name
    From Information_Schema.Table_Constraints Where Constraint_Type='FOREIGN KEY'

    Open curAllForeignKeys
    Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName
    While @@FETCH_STATUS=0
    Begin
    Set @SQL = 'ALTER TABLE ' + @TableName + ' NOCHECK CONSTRAINT ' + @ConstraintName
    Print @SQL
    -- Execute(@SQL)
    Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName
    End

    Declare curAllTables Cursor For Select Table_Name From Information_Schema.Tables Where TABLE_TYPE='BASE TABLE'
    Open curAllTables
    Fetch Next From curAllTables INTO @TableName
    While @@FETCH_STATUS=0
    Begin
    Set @SQL = 'DELETE FROM ' + @TableName
    If @ResetIdentity = 1 AND OBJECTPROPERTY (OBJECT_ID(@TableName),'TableHasIdentity')=1

    Set @SQL = @SQL + '; DBCC CHECKIDENT(''' + @TableName + ''',RESEED,0)'
    -- Execute(@SQL)
    Print @SQL
    Fetch Next From curAllTables INTO @TableName
    End

    Fetch First From curAllForeignKeys INTO @TableName,@ConstraintName
    While @@FETCH_STATUS=0
    Begin
    Set @SQL = 'ALTER TABLE ' + @TableName + ' CHECK CONSTRAINT ' + @ConstraintName
    -- Execute(@SQL)
    Print @SQL
    Fetch Next From curAllForeignKeys INTO @TableName,@ConstraintName
    End

    Close curAllTables
    Deallocate curAllTables
    Close curAllForeignKeys
    Deallocate curAllForeignKeys
    End
    SET NOCOUNT OFF
    Please help...
  2. satya Moderator

    Why not simply script out these 10 records data as a file and simply truncate all the tables, then apply this 10-rows script to ensure data exists as expected.
  3. sqlderby Member

    Is there any tool(s) or method to do this as export utility of SQL Server do not give data limitation UI to export or import...
  4. satya Moderator

  5. sqlderby Member

    We are using SQL Server 2005 and using no tools....
    Generating script option do not give us option of no. of records limits in export or import....
    well I am try to write a script that give us tables with and without identity column. And then generate the deletion script with where clause of no. of records limits.. i hope that this will works... and share with all here...
    Mean while any suggestion will be helpful...
    Thanks
  6. satya Moderator

    Canyou check you are not using SSMSE (Express edition Management studio), above link is correct and using server edition's SSMS should give you the capability.
  7. rohit2900 Member

    Hi,
    You can export records using data export import wizard and it will give ou two options either export all or write a query to select the data. here you can write your query for these 10 records. then once your exported all your data. then you can truncate your tables.
    I truly agree with satya as we shud always seek the easier way not the complicated one.
    Tell me if this helps.
    Rohit

Share This Page