SQL to Drop All Non-System Objects From Master DB

We've all been there - well I have anyway. You've just created your script to generate all the required objects for your database.  You run the script in the new environment.  But wait, you forgot to set the database!  All the objects have been created in the master db instead!

The number of times this has happened to me and to others I have worked with is infuriating.  I've sat there watching people spend ages deleting one by one all the db objects using Management Studio, stumbling when there are dependencies, and generally wasting a lot if time.

To solve this problem I have put together a short script to generate all the required drop statements. Fortunately, the sys.objects table contains a handy "is_ms_shipped" column that makes life much simpler:

SELECT

'IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''[dbo].['+Name+']''))'+ CHAR(10)+ 'DROP '

+ CASE WHEN  type = 'U' THEN 'TABLE '

       WHEN  type = 'P' THEN 'PROCEDURE '

       WHEN  type ='FN' THEN 'FUNCTION '

       WHEN  type = 'V' THEN 'VIEW '

  END

+ Name + CHAR(10) + 'GO' + CHAR(10)

FROM  Master.sys.objects

WHERE is_ms_shipped <> 1

AND TYPE IN ('U','P','FN','V')

ORDER BY

CASE  WHEN type = 'P'  THEN 1

      WHEN type = 'V'  THEN 2

      WHEN type = 'FN' THEN 3

      WHEN type = 'U'  THEN 4

END

This script will generate all the DROP statements for the user-defined objects in the master db.  These need to be copied into a new window (easiest if you have been outputted the results to text rather than to grid in SSMS) and executed.

If there are any foreign key constraints, however, the above script will generate an error.  To handle this either execute all the drop statements over and over until the errors cease (each subsequent execution of the drop statements will delete additional tables as each foreign key table is dropped), or, prior to executing the above script, execute the following:

SELECT 'ALTER TABLE [' + SCHEMA_NAME(schema_id) + '].[' + OBJECT_NAME(fk.parent_object_ID) + '] DROP CONSTRAINT ' + fk.name

+ CHAR(10) + 'GO' +CHAR(10)

FROM sys.foreign_keys fk

WHERE is_ms_shipped = 0

This will generate the necessary statements that need to be run in order to drop all the constraints. Copy the output to a new query window and execute.

Truncate vs Delete - Myth vs Reality

I have been asked many times (often in interviews) the following question:

Q. What is the difference between Truncate and Delete in SQL Server?

The answer that, in my experience, is most expected and most often given is that Delete allows you roll back the data you have wiped, while Truncate does not - Truncate is irreversible.  A quick search on google gives a number of posts that say as much.

Simple.  But wrong!  Just try this simple test.

1. Insert records into a table. 
2. Open a transaction. 
3. Truncate the table. 
4. View the contents of the table.
5. Rollback the transaction.
6. View the contents of the table.

 The code below is a very simple example:

CREATE TABLE MyTable (col1 CHAR(1), col2 INT)

GO

INSERT INTO MyTable (col1, col2)

SELECT 'a', 1 UNION ALL SELECT 'b', 2 UNION ALL SELECT 'c', 3

GO

SELECT * FROM MyTable --(3 row(s) affected)

BEGIN TRAN

TRUNCATE TABLE MyTable

GO

SELECT * FROM MyTable --(0 row(s) affected)

ROLLBACK

SELECT * FROM MyTable --(3 row(s) affected)

Before the transaction is rolled back, there are no rows in the table, but after the rollback all three records have returned, despite having used Truncate to remove them.  Clearly Truncate is reversible!

So what actually is the difference between Truncate and Delete?  There are a number of general differences, but the relevant one here is the following:

When rows are DELETE'd the operation is logged for each row removed. TRUNCATE logs only the deallocation of the data pages rather than the rows themselves, making it a much faster operation.  

There are many other differences related to permission levels, seeding, triggers, contraints etc. That's for a future post.  For now, the point to note is that TRUNCATE can indeed be rolled back, and the myth has been busted!