tsql - 事务 sql 查询以删除所有以 aspnet_ 开头的表

Question

这个问题在这里已经有了答案:





Delete all data in SQL Server database

(12 个回答)


7年前关闭。




我在网站主机上创建了一个新数据库，它给了我一堆表、 View 和存储过程，这些对我的需求来说是多余的(我使用自定义成员资格提供程序)。

所有的表和存储过程都以 dbo.aspnet_ 开头

所有 View 都以 dbo.vw_aspnet_ 开头

我对 sql 语法不是很熟悉，但是是否有一个 sql 命令可以删除所有以字符序列开头的表、 View 和存储过程。

谢谢

编辑

字面上有许多无用的数据库对象，但在其中，我有一些有用的数据库对象(当我开始设置它时，我认为它是一个空的数据库)我想保留。关键是名称以 aspnet_ 开头

请注意这与删除所有表不同

Answer 1

以防万一其他人最终处于相同的位置 - 这是我最终使用的 SQL，它成功地对我的数据库进行了“de-asp”化，但留下了有用的(非 ASP)数据库对象

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