Have you ever wanted to clean the data out of a SQL Server database quickly?
Well, this little piece of T-SQL code will create a script that you can copy and paste into a query window and do just that.

-- Declare the variables used by the SP
DECLARE @TableName VARCHAR(125)
-- Create a cursor to loop through with table names
DECLARE cTableList CURSOR
    FOR SELECT Table_Name
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE   TABLE_TYPE = 'Base Table'
        ORDER BY Table_Name
-- open the cursor so we can use it
OPEN cTableList
-- get the first row in the result set of the cursor
FETCH NEXT FROM cTableList INTO @TableName
-- set up the loop, and loop until no more records
WHILE (@@FETCH_STATUS = 0)
    BEGIN
        -- print the result for copying later
        PRINT 'TRUNCATE TABLE dbo.' + @TableName + ';'
        -- get the next row from the result set
        FETCH NEXT FROM cTableList INTO @TableName
    END
-- close the cursor and deallocate it
CLOSE cTableList
DEALLOCATE cTableList

Most DBAs will frown on using a cursor for anything, but, sometimes it is the only way of doing it. Those of you that are accustomed to database programs like MS Access can relate the SQL cursor to a recordset. They are extremely handy things to use. But, use cursors sparingly, NEVER leave them open, and ALWAYS DEALLOCATE them. Otherwise, they will persist until you do close and deallocate them.
Once you run the code above, you will get a code listing in your Messages window similar to this:
TRUNCATE TABLE dbo.tbl_Activities_Legend;
TRUNCATE TABLE dbo.tbl_Admin_Data_Password;
TRUNCATE TABLE dbo.tbl_Admin_Global_Defaults;
TRUNCATE TABLE dbo.tbl_Admin_License_Info;
TRUNCATE TABLE dbo.tbl_Admin_License_Rates;
TRUNCATE TABLE dbo.tbl_Admin_Licensed_To;
TRUNCATE TABLE dbo.tbl_Admin_Modules_Local;
Now you can copy the code generated in your Message window to your clipboard, open a new query window, and paste it into the window. When you click on the execute button, all of the data in your database will be gone.
Do I need to say it? BE CAREFUL!!! This will remove all of your data. And, since it bypasses the log file, you cannot recover it from the log.
If you have foreign key relationships setup, the TRUNCATE TABLE will not work. You should change the following code:

PRINT 'TRUNCATE TABLE dbo.' + @TableName + ';'

PRINT 'DELETE FROM dbo.' + @TableName + ';'

TRUNCATE is faster than DELETE simply because it bypasses the log file. DELETE will log the delete of each row of data. So, with a lot of work, the data could be recovered.
Again, be careful. This either way, all of the data is removed from every table in the code generated by this stored procedure.
Robert L. Stewart is a full time DBA and SQL Server programmer at a small company outside of Houston. He has been involved with HAL-PC since MS Access 1.0 came out, and, has been the SIG leader for most of the Access SIGs. He is also the SIG leader for the Digital Imaging SIG that meets at the HAL-PC offices. He can be reached at robert@WeBeDB.com if you have any questions.