以编程方式删除 SQL 2008 数据库中具有特定前缀名称的所有表

发布于 2024-11-18 02:56:28 字数 488 浏览 5 评论 0原文

我的 SQL 2008 数据库中有 3000 多个表，其名称如下所列，全部以 tempBinary_ 开头，我需要以编程方式删除它们，我该怎么做？我不知道我是否更喜欢 SQL 脚本中的解决方案或使用 LINQtoSQL，我想两者都很好。

tempBinary_002c90322f4e492795a0b8a14e2f7c99 tempBinary_0039f7db05a9456f96eb3cd6a788225a tempBinary_0057da9ef0d84017b3d0bbcbfb934fb2

我已经在列上使用过类似的方法，但我不知道它是否也适用于表名。也许像这样的东西，使用LIKE，可以做到吗？我不知道。

Use [dbo].[database_name]
DROP TABLE table_name
WHERE table_name LIKE 'tempBinary_%'

有什么想法吗？

原文

I have 3000+ tables in my SQL 2008 database with names like listed below, that all starts with tempBinary_, that I need to delete programmatically, how do I do that?
I don't know if I prefer the solution in a SQL-script or with use of LINQtoSQL, i guess both are fine.

tempBinary_002c90322f4e492795a0b8a14e2f7c99
tempBinary_0039f7db05a9456f96eb3cd6a788225a
tempBinary_0057da9ef0d84017b3d0bbcbfb934fb2

I've used Like before on columns, but I don't know if it good for table names too.
Maybe something like this, where LIKE is used, can do it? I don't know.

Use [dbo].[database_name]
DROP TABLE table_name
WHERE table_name LIKE 'tempBinary_%'

Any ideas?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

只有一腔孤勇 2024-11-25 02:56:28

declare @stmt varchar(max) = ''
declare @tbl_name varchar(255)


DECLARE tbl_cursor CURSOR  FORWARD_ONLY READ_ONLY
    FOR select name 
        from sysobjects 
        where xtype='u' and name like 'tempBinary%'
OPEN tbl_cursor
FETCH NEXT FROM tbl_cursor
INTO @tbl_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    set @stmt = @stmt + 'drop table ' + @tbl_name + ';' +  CHAR(13)


    FETCH NEXT FROM tbl_cursor 
    INTO @tbl_name
end
CLOSE tbl_cursor;
DEALLOCATE tbl_cursor;    

execute sp_sqlexec @stmt

declare @stmt varchar(max) = ''
declare @tbl_name varchar(255)


DECLARE tbl_cursor CURSOR  FORWARD_ONLY READ_ONLY
    FOR select name 
        from sysobjects 
        where xtype='u' and name like 'tempBinary%'
OPEN tbl_cursor
FETCH NEXT FROM tbl_cursor
INTO @tbl_name;

WHILE @@FETCH_STATUS = 0
BEGIN
    set @stmt = @stmt + 'drop table ' + @tbl_name + ';' +  CHAR(13)


    FETCH NEXT FROM tbl_cursor 
    INTO @tbl_name
end
CLOSE tbl_cursor;
DEALLOCATE tbl_cursor;    

execute sp_sqlexec @stmt

回复收藏 0 原文

~没有更多了~