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!
Miss this week's Weekly Webinar? This week was another turn for Sitefinity CMS, and that means another great webinar from Sitefinity Evangelist Gabe Sumner. Gabe covered "Multi-language and Localization Support" for Sitefinity this week, so anyone who has been interested in using Telerik's CMS in a multi-language or international environment should find the content interesting. Topics covered include:
I don't usually "bundle" news items in to a single post, but since it's Friday and I have a couple of releases to announce, I'll make an exception.
This post is monumental for a couple of reasons. First, this is the 300th post on Telerik Watch since its 
As long as we're 
