Telerik Watch: Code: SQL script for dropping all Sitefinity objects

sitefinity-sql I don’t often post code snippets on Telerik Watch. I tend to save posts like that for the official Telerik Blogs or my more general Code Campground blog. But today I’ll make an exception since my snippet is covering a topic I rarely get to cover these days: Telerik Sitefinity CMS. I used to spend more time covering Sitefinity on Telerik Watch, but as that platform has grown and become increasingly popular (and powerful), I’ve let the experts (like Sitefinity Evangelist Gabe Sumner) bring you the regular news, updates, and snippets.

Today I faced the challenge of upgrading an old Sitefinity site (v3.0 – used by my wife for her class website) to the latest and greatest v3.6 SP2. Like a new OS install, I wanted to start with a “clean slate” instead of trying to work through an upgrade, and to do that I needed to remove all Sitefinity v3.0 objects from my SQL Server database.

Should be easy, but I’ve got a couple of challenges I’m sure some of you face:

My SQL Server is hosted, meaning I don’t have complete control over the server to drop and recreate entire databases
More significantly, this database is hosting tables for multiple applications- not just Sitefinity. I need to leave my other DB objects in-tact and only remove my Sitefinity-specific objects.

To solve my problem, I need a SQL script that will go through my database and delete all Sitefinity tables, stored procedures, and relationships. Thankfully, Sitefinity prefixes all of the objects it creates with either “sf_” or “telerik_”, so we can us that fact to create a script that will delete all objects that meet our prefix search criteria.

Continue reading to see SQL solution

Based on a helpful “generic” script on Paige Cook’s blog, the following script can be used delete all (and only) Sitefinity objects in a SQL Server database:

-- Drop all Sitefinity stored procs
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'P' AND category = 0
   AND ([name] LIKE 'sf_%' OR [name] LIKE 'telerik_%') 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 AND ([name] LIKE 'sf_%' OR [name] LIKE 'telerik_%') ORDER BY [name])
END
GO

-- Drop any Sitefintiy views (none in current default install)
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'V' AND category = 0
   AND ([name] LIKE 'sf_%' OR [name] LIKE 'telerik_%') 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 AND ([name] LIKE 'sf_%' OR [name] LIKE 'telerik_%') ORDER BY [name])
END
GO


-- Drop any Sitefinity functions (none in current default install) 
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 AND ([name] LIKE 'sf_%' OR [name] LIKE 'telerik_%') 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 AND ([name] LIKE 'sf_%' OR [name] LIKE 'telerik_%') ORDER BY [name])
END
GO

-- Drop all Sitefinity 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'
   AND (TABLE_NAME LIKE 'sf_%' OR TABLE_NAME LIKE 'telerik_%') 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'
   AND (TABLE_NAME LIKE 'sf_%' OR TABLE_NAME LIKE 'telerik_%') ORDER BY TABLE_NAME)
END
GO

-- Drop all Sitefinity 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'
   AND (TABLE_NAME LIKE 'sf_%' OR TABLE_NAME LIKE 'telerik_%') 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'
   AND (TABLE_NAME LIKE 'sf_%' OR TABLE_NAME LIKE 'telerik_%') ORDER BY TABLE_NAME)
END
GO

-- Drop all Sitefinity tables
DECLARE @name VARCHAR(128)
DECLARE @SQL VARCHAR(254)
SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE [type] = 'U' AND category = 0
   AND ([name] LIKE 'sf_%' OR [name] LIKE 'telerik_%') 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 AND ([name] LIKE 'sf_%' OR [name] LIKE 'telerik_%') ORDER BY [name])
END
GO

When this runs it will delete about 670 objects from your database, printing the name of each dropped object to the query output window. If you refresh your objects and still see Sitefinity objects in your database, just run the script a second time and that should fully clean your database. You now have a clean starting point to re-install Sitefinity in your database, and you’ve done your cleaning without touching any of your other, non-Sitefinity database objects. Hope this helps!