Drop All Objects in a SQL Server Database

Here is a script that I created that will drop all the objects in a SQL Server database. It was created using a lot of other scripts out on the Internet as inspiration. I like this one does because of the following:

• Does not use cursors (which a lot of the ones I saw did)
• Will drop the foreign key and primary key constraints on the tables prior to dropping the tables.

 

/* Drop all non-system stored procs */

DECLARE @name VARCHAR(128)

DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 ORDER BY [name])

WHILE @name is not null

BEGIN

    SELECT @SQL = 'DROP PROCEDURE [dbo].[' + RTRIM(@name) +']'

    EXEC (@SQL)

    PRINT 'Dropped Procedure: ' + @name

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])

END

GO

 

/* Drop all views */

DECLARE @name VARCHAR(128)

DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL

BEGIN

    SELECT @SQL = 'DROP VIEW [dbo].[' + RTRIM(@name) +']'

    EXEC (@SQL)

    PRINT 'Dropped View: ' + @name

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])

END

GO

 

 

/* Drop all functions */

DECLARE @name VARCHAR(128)

DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL

BEGIN

    SELECT @SQL = 'DROP FUNCTION [dbo].[' + RTRIM(@name) +']'

    EXEC (@SQL)

    PRINT 'Dropped Function: ' + @name

    SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] IN (N'FN', N'IF', N'TF', N'FS', N'FT') AND category = 0 AND [name] > @name ORDER BY [name])

END

GO

 

/* Drop all Foreign Key constraints */

DECLARE @name VARCHAR(128)

DECLARE @constraint VARCHAR(254)

DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

WHILE @name is not null

BEGIN

    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)

    WHILE @constraint IS NOT NULL

    BEGIN

        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT ' + RTRIM(@constraint)

        EXEC (@SQL)

        PRINT 'Dropped FK Constraint: ' + @constraint + ' on ' + @name

        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)

    END

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)

END

GO

 

/* Drop all Primary Key constraints */

DECLARE @name VARCHAR(128)

DECLARE @constraint VARCHAR(254)

DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

WHILE @name IS NOT NULL

BEGIN

    SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)

    WHILE @constraint is not null

    BEGIN

        SELECT @SQL = 'ALTER TABLE [dbo].[' + RTRIM(@name) +'] DROP CONSTRAINT ' + RTRIM(@constraint)

        EXEC (@SQL)

        PRINT 'Dropped PK Constraint: ' + @constraint + ' on ' + @name

        SELECT @constraint = (SELECT TOP 1 CONSTRAINT_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' AND CONSTRAINT_NAME <> @constraint AND TABLE_NAME = @name ORDER BY CONSTRAINT_NAME)

    END

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)

END

GO

 

/* Drop all tables */

DECLARE @name VARCHAR(128)

DECLARE @SQL VARCHAR(254)

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 ORDER BY [name])

WHILE @name IS NOT NULL

BEGIN

    SELECT @SQL = 'DROP TABLE [dbo].[' + RTRIM(@name) +']'

    EXEC (@SQL)

    PRINT 'Dropped Table: ' + @name

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])

END

GO

 

 

SQL Server: Restore Your Master

I have been playing around with scripting the drop of all objects within a database and I accidentally ran this today in Master database on my local SQLExpress 2005 instance unintentionally (cough, cough). I have had better days. :-( So I originally thought I was going to have to reinstall SQL Server Express to restore the database, but after a little digging on Google I found this article that shows you can do it with just the following command line:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Install>sqlcmd -E -S .\SQLEXPRESS -i instmsdb.sql

 

Whew, that saved me a lot of time. Hopefully, you won't make this same mistake, but if you do, this is a quick way to fix it.

Thou Shalt Not Mock Thyself

We had some Unit Tests in our application recently that were failing. We are using TypeMock.Net (now TypeMock Isolator) to mock out some of the objects in the tests. In tracking down the issue,  I learned that you should not mock yourself. To better explain this, please consider the following trivial class.

UPDATE: Please read the comment (and see the code) from Eli Lopian (founder of TypeMock) on how to mock the class without mocking the constructor. Thanks Eli!

using System.Configuration;

 

namespace SelfMocking

{

    public class MyClass

    {

        public readonly string Text1;

        public readonly bool IsValid;

 

        public string SettingValue1()

        {

            return System.Configuration.ConfigurationManager.AppSettings["Value1"];

        }

 

        public MyClass(string text1)

        {

            Text1 = text1;

            IsValid = !string.IsNullOrEmpty(Text1);

        }

    }

}

 

Then we will write the following test fixture and test to mock the call to the SettingValue1 property and return a mocked string value. To do that I am going to mock the MyClass object and return the mocked value for all calls to MyClass.SettingValue1. See the code below:

using System.Collections.Specialized;

using NUnit.Framework;

using NUnit.Framework.SyntaxHelpers;

using TypeMock;

 

namespace SelfMocking

{

    [TestFixture]

    public class MyClassTest

    {

        [TestFixtureSetUp]

        public void FixtureSetup()

        {

            MockManager.Init();

        }

 

        [TestFixtureTearDown]

        public void FixtureTearDown()

        {

            MockManager.Verify();

        }

 

        [Test]

        public void Test_MyClass()

        {

            MockMyClass();

            MyClass testMyClass = new MyClass("test1");

            Assert.That(testMyClass.SettingValue1(), Is.EqualTo("MockedValue1"));

            Assert.That(testMyClass.Text1, Is.Not.Null);

            Assert.That(testMyClass.IsValid, Is.True);

        }

 

        private void MockMyClass()

        {

            Mock mockMyClass = MockManager.Mock(typeof (MyClass));

            mockMyClass.AlwaysReturn("SettingValue1", "MockedValue1");

        }

    }

}

When I attempt to execute the unit test Test_MyClass, it returns an fails with the following message:

NUnit.Framework.AssertionException:   Expected: not null  But was:  null

So it is correctly mocking the SettingsValue1() call, but the property Text1 is not being set. If I debug the test and step through the code as it is being executed, I see that the constructor for MyClass is never being executed, even though my test is calling it. The reason it is not being executed, is that the first thing I do is create a mock of MyClass, but I do not instruct the mock object to mock the constructor, so when I call the constructor, it is just ignored. Therefore, the Text1 property is never set and is in fact still Null.

In order to properly mock this, we need to go a level deeper and actually mock the AppSettings call on the System.Configuration.ConfigurationManager class. Below is the revised code:

using System.Collections.Specialized;

using NUnit.Framework;

using NUnit.Framework.SyntaxHelpers;

using TypeMock;

 

namespace SelfMocking

{

    [TestFixture]

    public class MyClassTest

    {

        private NameValueCollection nameValueCollection = new NameValueCollection();

 

        [TestFixtureSetUp]

        public void FixtureSetup()

        {

            MockManager.Init();

            nameValueCollection["Value1"] = "MockedValue1";

        }

 

        [TestFixtureTearDown]

        public void FixtureTearDown()

        {

            MockManager.Verify();

        }

 

        [Test]

        public void Test_MyClass()

        {

            MockAppSettings();

            MyClass testMyClass = new MyClass("test1");

            Assert.That(testMyClass.SettingValue1(), Is.EqualTo("MockedValue1"));

            Assert.That(testMyClass.Text1, Is.Not.Null);

            Assert.That(testMyClass.IsValid, Is.True);

        }

 

        private void MockAppSettings()

        {

            Mock mockConfigurationManager = MockManager.Mock(typeof (System.Configuration.ConfigurationManager));

            mockConfigurationManager.ExpectGetAlways("AppSettings", nameValueCollection);

        }

    }

}

As you can see in this new version, I am mocking ConfigurationManager and have created a new NameValueCollection into which I have set the key Value1 = "MockedValue1" and then when the class calls AppSettings, the MockManager will return my NameValueCollection instead.

So, keep in mind that if you are mocking objects, it is never a good idea to mock the class that you are actually trying to test, as it could have unexpected results.

Silverlight Cross-Domain Calls

I have been playing around with the latest Silverlight 2.0 Beta. Specifically, working through the excellent tutorial on Scott Guthrie's blog. (By the way, Matt Berseth has provided a working example of this tutorial available here.) So, after doing a few sections of this, I decided that I wanted to be able to pull some of my own data, instead of using the Digg Service. So I wrote a small REST service using WCF with.Net 3.5. I was able to quickly get this up and running and then create a new Silverlight app to consume this service. However, when I would try to connect to the REST service using my Silverlight app, I was always getting an "Download Falure" error.

I did some digging into this and found that in order for a Silverlight (or Flash) app coming from one domain to consume data from services on another domain, there must be a policy mechanism on the service domain that grants access to the domain of the application. This policy must be available from the root of the domain. Unfortunately, http://localhost:8001/ and http://localhost:8002/ are considered different domains. So in order for my Silverlight app on port 8001 to talk to the REST service on port 8002, I needed to provide this policy access. I found this post by Carlos Figueria that covers this scenario and how to setup your service to host the policy files for Silverlight and Flash when your service is self-hosted and in a web site.

Working with DataTables

I recently had to write a method that would take a single row from a DataTable and expand that entry based on a start and end date and then determine which rows intersected with a range of dates.

The process to accomplish this seemed fairly straight forward:

• Clone the original table.
• Populate the new table with the correct number of entries.
• Add the new rows back that fall within the range of dates.

 

Original Item:

ItemId	StartDate	EndDate	Time
12345	3/5/2008	3/8/2008	8:00 AM

 

Expanded Items:

ItemId	StartDate	EndDate	Time
12345	3/5/2008	3/8/2008	8:00 AM
12345	3/6/2008	3/8/3008	8:00 AM
12345	3/7/2008	3/8/2008	8:00 AM
12345	3/8/2008	3/8/2008	8:00 AM

 

Expected Results for Range of 3/6/2008 to 3/8/2008

ItemId	StartDate	EndDate	Time
12345	3/6/2008	3/8/3008	8:00 AM
12345	3/7/2008	3/8/2008	8:00 AM
12345	3/8/2008	3/8/2008	8:00 AM

 

When I originally started out with creating this method my thought was to create only the rows that would be needed. So I began writing a bunch of branching logic that would compare the start date and the range start, compare the range end and end date, etc.; trying to determine how many days needed to be created and what day to start with. It quickly became very complicated and difficult for me to determine what I was doing. So I went back to the drawing board and figured out that with the help of the DataTable and DataRow classes in .NET that I could just expand the entire original item and then just select the rows where that intersected with my date range.

 

Code

Here is version of the method that I ended up with, I basically just removed the business logic that was specific to our application.

 

        public DataTable ExpandRows(DataTable original, DateTime rangeStart, DateTime rangeEnd)

        {

            DataTable expanded = original.Clone();

 

            //Assumes only one row for demo purposes

            DateTime itemStart = DateTime.Parse(original.Rows[0]["StartDate"].ToString());

            DateTime itemEnd = DateTime.Parse(original.Rows[0]["EndDate"].ToString());

 

            int numDays = itemEnd.Subtract(itemStart).Days;

            for (int i = 0; i <= numDays; i++)

            {

                DataRow newRow = expanded.NewRow();

                newRow.ItemArray = original.Rows[0].ItemArray;

                newRow["StartDate"] = itemStart.AddDays(i);

                expanded.Rows.Add(newRow);

            }

 

            //select the rows that fall within the specified range of dates.

            DataRow[] addRows =

                expanded.Select(

                    string.Format("StartDate >= '{0}' AND StartDate <= '{1}'", rangeStart.ToShortDateString(),

                                  rangeEnd.ToShortDateString()));

 

            //Remove the original row,since we do not know if it falls within range.

            original.Rows.Remove(original.Rows[0]);

 

            //add expanded rows that fall within the range

            int numRowsToAdd = addRows.Length;

            for (int j = 0; j < numRowsToAdd; j++)

            {

                original.ImportRow(addRows[j]);

            }

 

            return original;

        }

Limiting Results

Because I was using this method on a search page and I wanted to limit the results, because if you have an item with a large start/end date span and as well as a large range start/end date span there cold be hundreds or thousands of rows in the table. To limit the results I used a technique that I found in the ASP.NET Forums that shows how to add a unique id to the table and only return the a specified number of rows. So here is the final code with the limit the number of rows. Below is the final code:

        public DataTable ExpandRows(DataTable original, DateTime rangeStart, DateTime rangeEnd, int maxRowCount)

        {

            DataTable expanded = original.Clone();

 

            //Assumes only one row for demo purposes

            DateTime itemStart = DateTime.Parse(original.Rows[0]["StartDate"].ToString());

            DateTime itemEnd = DateTime.Parse(original.Rows[0]["EndDate"].ToString());

 

            int numDays = itemEnd.Subtract(itemStart).Days;

            for (int i = 0; i <= numDays; i++)

            {

                DataRow newRow = expanded.NewRow();

                newRow.ItemArray = original.Rows[0].ItemArray;

                newRow["StartDate"] = itemStart.AddDays(i);

                expanded.Rows.Add(newRow);

            }

 

            //select the rows that fall within the specified range of dates.

            DataRow[] addRows =

                expanded.Select(

                    string.Format("StartDate >= '{0}' AND StartDate <= '{1}'", rangeStart.ToShortDateString(),

                                  rangeEnd.ToShortDateString()));

 

            //Remove the original row,since we do not know if it falls within range.

            original.Rows.Remove(original.Rows[0]);

 

            //add expanded rows that fall within the range

            int numRowsToAdd = addRows.Length;

            for (int j = 0; j < numRowsToAdd; j++)

            {

                original.ImportRow(addRows[j]);

            }

 

 

            if (maxRowCount > 0)

            {

                if (original.Rows.Count > maxRowCount)

                {

                    DataTable autoIds = new DataTable();

                    DataColumn column = new DataColumn();

                    column.DataType = typeof(Int32);

                    column.AutoIncrement = true;

                    column.AutoIncrementSeed = 1;

                    column.AutoIncrementStep = 1;

                    column.ColumnName = "AutoRowId";

                    autoIds.Columns.Add(column);

 

                    autoIds.Merge(original, true, MissingSchemaAction.AddWithKey);

 

                    DataTable truncated = original.Clone();

                    DataRow[] limitedRows = autoIds.Select(string.Format("AutoRowId<={0}", maxRowCount));

 

                    for (int z = 0; z < maxRowCount; z++)

                    {

                        truncated.ImportRow(limitedRows[z]);

                    }

 

                    return truncated;

                }

            }

 

            return original;           

        }

 

 

Lessons Learned

Here are some of the things that I learned along the way.

• Sometimes you need to step back and reassess the path you have initially chosen, it may not always be the best one. And in this case I came up with something that ended up being much simpler and more efficient.
• The importance of Unit Tests. I have been experimenting with using Test Driven Development (TDD) recently. So I was decided to switch my approach I was able to feel confident that the code was still working as expected, because I had immediate feedback as I made changes.
• You cannot directly insert a datarow from one table into another. If you try to do this you will get the error:  "This row already belongs to another table". Instead, use ImportRow to copy the row into another DataTable.
• I was using a DataView to do some sorting on the DataTable like the following,

              DataView dataView = new DataView(original, string.Empty, "StartDate ASC", DataViewRowState.OriginalRows);

However, because I was removing the original rows and adding new ones, the table was missing the original rows and the newly added ones; Because as the name states, it was looking at the original rows in the table... I needed to use the setting DataViewRowState.CurrentRows to display the rows as they currently existed in the table.

Silverlight 2 & ASP.NET MVC Preview 2

Today at Mix08, Microsoft officially announced and released the latest updates to Silverlight and the ASP.NET MVC Framework. You can download them via the following links:

Silverlight 2 - Includes the following:

• Silverlight 2 Beta 1
• Silverlight 2 SDK Beta 1
• Silverlight Tools Beta 1 for VS 2008
• KB949325 for VS 2008.

ASP.NET MVC Framework - Preview 2 

Make sure you also check out the ReadMe that describes some of known issues, etc. related to both Silverlight 2 and ASP.NET MVC Preview 2.