transact sql query to drop all tables starting with aspnet_

Question

I created a new database on a website host, and it gave me a heap of tables, views and stored procedures which are superfluous to my needs (I use a custom membership provider).

all the tables and stored procedures start with dbo.aspnet_

all the views start with dbo.vw_aspnet_

I am not very familiar with sql syntax, but is there an sql command to drop all tables, views and stored procedures starting with a sequence of characters.

Thank you

edit

There are literally scores of useless database objects, but in amongst them, I have useful database objects (I assumed it was an empty databse when I started setting it up) I want to keep. the key is the name STARTS WITH aspnet_

please note this is different from remove all tables

Ahh I think I see why you were down-voted - the dig deal is not referring to other peoples publicly posted code, but it is polite to acknowledge the original author. — Brent, Jun 14 '13 at 08:36
I just had a look at the documentation - looks like you are right - thanks (I didn't know of the existence of this tool). — Brent, Jun 14 '13 at 09:14

score 3 · Answer 1 · answered Jun 14 '13 at 09:08

just in case someone else ends up in the same position - here is the SQL I eventually used which successfully 'de-asp'ified my database, but left the useful (non-ASP) database objects

DECLARE @name VARCHAR(128), @SQL VARCHAR(254), @prefixLen int, @prefix nvarchar(30);
SET @prefix = 'aspnet_';
SET @prefixLen = LEN(@prefix);

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE LEFT(name,@prefixLen) = @prefix AND [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 LEFT(name,@prefixLen) = @prefix AND [type] = 'P' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all views */
DECLARE @name VARCHAR(128), @SQL VARCHAR(254), @prefixLen int, @prefix nvarchar(30);
SET @prefix = 'vw_aspnet_';
SET @prefixLen = LEN(@prefix);

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE LEFT(name,@prefixLen) = @prefix AND [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 LEFT(name,@prefixLen) = @prefix AND [type] = 'V' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

/* Drop all functions */
DECLARE @name VARCHAR(128), @SQL VARCHAR(254), @prefixLen int, @prefix nvarchar(30);
SET @prefix = 'aspnet_';
SET @prefixLen = LEN(@prefix);

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE LEFT(name,@prefixLen) = @prefix AND [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 LEFT(name,@prefixLen) = @prefix AND [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), @SQL VARCHAR(254), @prefixLen int, @prefix nvarchar(30), @constraint VARCHAR(254);
SET @prefix = 'aspnet_';
SET @prefixLen = LEN(@prefix);

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE LEFT([TABLE_NAME],@prefixLen) = @prefix AND 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 LEFT([TABLE_NAME],@prefixLen) = @prefix AND 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 LEFT([TABLE_NAME],@prefixLen) = @prefix 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 LEFT([TABLE_NAME],@prefixLen) = @prefix AND constraint_catalog=DB_NAME() AND CONSTRAINT_TYPE = 'FOREIGN KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all Primary Key constraints */
DECLARE @name VARCHAR(128), @SQL VARCHAR(254), @prefixLen int, @prefix nvarchar(30), @constraint VARCHAR(254);
SET @prefix = 'aspnet_';
SET @prefixLen = LEN(@prefix);

SELECT @name = (SELECT TOP 1 TABLE_NAME FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  LEFT([TABLE_NAME],@prefixLen) = @prefix AND 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 LEFT([TABLE_NAME],@prefixLen) = @prefix 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 LEFT([TABLE_NAME],@prefixLen) = @prefix 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 LEFT([TABLE_NAME],@prefixLen) = @prefix AND CONSTRAINT_TYPE = 'PRIMARY KEY' ORDER BY TABLE_NAME)
END
GO

/* Drop all tables */
DECLARE @name VARCHAR(128), @SQL VARCHAR(254), @prefixLen int, @prefix nvarchar(30);
SET @prefix = 'aspnet_';
SET @prefixLen = LEN(@prefix);

SELECT @name = (SELECT TOP 1 [name] FROM sysobjects WHERE  LEFT([name],@prefixLen) = @prefix AND [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 LEFT([name],@prefixLen) = @prefix AND [type] = 'U' AND category = 0 AND [name] > @name ORDER BY [name])
END
GO

transact sql query to drop all tables starting with aspnet_

edit

1 Answers1