I have a database with four schemas, and 130 tables. All tables have foreign key constraints. I need to write a script that will purge all of these tables without conflicts against the foreign keys.

Is there a feature in Management Studio, VS 2010/2012, or some other utility that will allow me to quickly create such a script? 

Thanks.

cdun2

Why don't you just script out all objects with the Object Explorer Script Wizard and build a new empty database?

Take a look at this blog post

Delete all data in database (when you have FKs)

Hi, You could also try something like below.

SELECT 'TRUNCATE TABLE ' + S.name + '.' + T.name FROM sys.tables T
INNER JOIN sys.schemas S
  ON S.[schema_id] = T.[schema_id]
GO

No, truncating tables isn't going to work when you have FK constraints. I agree with Kallman, either you want an empty database, or you do not want the FK constraints. But could you tell us what the precise purpose is. Maybe that might help us find another solution.

Also have a look at the discussion in this thread: drop tables in database

I know that dropping tables is different from truncating tables, but maybe some remarks in that thread can help. Could you, for instance, create a copy of your database from which you drop all data? If you need an empty database, you simply can use a backup of this "empty database".

If you like, I could whip up a script to delete all data from all tables, but I just am not sure if that is what you would like to do. Is this a production environment, for instance?

Why don't you just script out all objects with the Object Explorer Script Wizard and build a new empty database?

1. First disable the FKs
2. Then do your conditional delete
3. Enable the FKs again
4. Check the FKs in order to get them trusted again

If not all of your FKs can get trusted, the set of data you are left with is inconsistent.

If you need examples, please say so.

I will repeat one of my questions. Is this a production environment? If so, I wouldn't recommend doing such drastic things as deleting entire sets of data.

I head some time left, so here is the script. If you also need to disable and enable triggers, just let me know and we will expand the script.

SET NOCOUNT ON;

DECLARE @Statement varchar(1000);

/* Remove the statement below if you are really going to use this script. */
/* The statement below just helps you see what is going on with the constraints. */
SELECT TOP 5 [name], is_disabled, is_not_trusted FROM sys.foreign_keys ORDER BY [name], parent_object_id;

DECLARE cr_Disable_FKs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
    SELECT 'ALTER TABLE ' + SCHEMA_NAME(TAB.[schema_id]) + '.' + TAB.[name] + ' NOCHECK CONSTRAINT ' + FK.[name] + ';'
    FROM sys.foreign_keys AS FK
    INNER JOIN sys.tables AS TAB
      ON TAB.[object_id] = FK.parent_object_id;
OPEN cr_Disable_FKs;
FETCH cr_Disable_FKs INTO @Statement;
PRINT 'Disable FKs';
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @Statement;
    EXECUTE (@Statement);
    FETCH cr_Disable_FKs INTO @Statement;
END;
PRINT ' ';
CLOSE cr_Disable_FKs;
DEALLOCATE cr_Disable_FKs;

/* Remove the statement below if you are really going to use this script. */
/* The statement below just helps you see what is going on with the constraints. */
SELECT TOP 5 [name], is_disabled, is_not_trusted FROM sys.foreign_keys ORDER BY [name], parent_object_id;

/* This is the place your delete statements should be inserted */
PRINT '<Insert your delete statements here>';
PRINT ' ';

DECLARE cr_Enable_FKs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
    SELECT 'ALTER TABLE ' + SCHEMA_NAME(TAB.[schema_id]) + '.' + TAB.[name] + ' CHECK CONSTRAINT ' + FK.[name] + ';'
    FROM sys.foreign_keys AS FK
    INNER JOIN sys.tables AS TAB
      ON TAB.[object_id] = FK.parent_object_id;
OPEN cr_Enable_FKs;
FETCH cr_Enable_FKs INTO @Statement;
PRINT 'Enable FKs';
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @Statement;
    EXECUTE (@Statement);
    FETCH cr_Enable_FKs INTO @Statement;
END;
PRINT ' ';
CLOSE cr_Enable_FKs;
DEALLOCATE cr_Enable_FKs;

/* Remove the statement below if you are really going to use this script. */
/* The statement below just helps you see what is going on with the constraints. */
SELECT TOP 5 [name], is_disabled, is_not_trusted FROM sys.foreign_keys ORDER BY [name], parent_object_id;

DECLARE cr_Check_FKs CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY FOR
    SELECT 'ALTER TABLE ' + SCHEMA_NAME(TAB.[schema_id]) + '.' + TAB.[name] + ' WITH CHECK CHECK CONSTRAINT ' + FK.[name] + ';'
    FROM sys.foreign_keys AS FK
    INNER JOIN sys.tables AS TAB
      ON TAB.[object_id] = FK.parent_object_id;
OPEN cr_Check_FKs;
FETCH cr_Check_FKs INTO @Statement;
PRINT 'Make FKs trusted again';
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @Statement;
    EXECUTE (@Statement);
    FETCH cr_Check_FKs INTO @Statement;
END;
CLOSE cr_Check_FKs;
DEALLOCATE cr_Check_FKs;

/* Remove the statement below if you are really going to use this script. */
/* The statement below just helps you see what is going on with the constraints. */
SELECT TOP 5 [name], is_disabled, is_not_trusted FROM sys.foreign_keys ORDER BY [name], parent_object_id;
GO