There is already an object named ‘#xxxx’ in the database.

这个案例是前几天同事遇到的一个案例,在存储过程中删除了一个临时表,然后重新创建这个临时表时遇到there is already an object named ‘xxxx’ in the database.”这样的错误。下面简单演示一下这个案例(不用存储过程,而是直接用简单的sql语句重现)

 

 

create table #temp_test( id int, name varchar(32));
 
 
if  exists (select 1 from tempdb..sysobjects where name='#tmp_test')
begin
    drop table #temp_test;
end
go
 
 
create table #temp_test( id int, name varchar(32));

 

 

 

 

 

   同事很是不解,问我为什么在存储过程里面删除了这个临时表居然不能重建呢? 其实这里面并没有什么玄机,而是仅仅犯了逻辑错误。上面这个sql语句,其实永远也不会删除这个临时表。 

 

   原因很简单,临时表#temp_test在tempdb..sysobjects里面存储的名字为#temp_test_________________________________________________________________0000000000ee,所以上述脚本犯了一个逻辑错误: 开发人员以为临时表被删除了,其实实质上永远不会执行drop table #temp_test这句sql。存储在tempdb的sysobjects表中的临时表,其全名由 create table 语句中指定的表名和系统生成的数字后缀组成。为了允许追加后缀,为本地临时表指定的table_name不能超过 116 个字符(关于为什么是116字符,也是有原因的,此处不展开)

 

 

both regular and delimited identifiers must contain from 1 through 128 characters. for local temporary tables, the identifier can have a maximum of 116 characters.

 

 

 

那么为什么数据库自动会给临时表这样命名呢? 因为本地临时表是对当前连接(当前会话)可见的。但是任意会话都可以创建同样名字的临时表,那么临时表的元数据在数据库内部存储的时候,使用同样的名字就不能定位和区别,所以设计上,为了区别不同会话下同样命名的临时表,在tempdb..sysobjects里面临时表会保存的是数据库自动生成的名字(#临时表名+下划线+12位的十六进制字符),如下测试所示,在另外一个会话中,我们创建一个同样名字的临时表,然后我们去数据库检查,你会发现tempdb..sysobjects中有两个对象,他们名字的后缀是不一样的。

 

select @@spid;

go

create table #temp_test( id int, name varchar(32));

 

 

 

 

那么要如何定位、查找当前会话是否创建了本地临时表对象呢? 其实使用object_id函数就ok了:

 

 

 

if object_id(‘tempdb..#temp_test’ ) is not null drop table #temp_test;

 

 

 

那么存储过程中使用上述脚本删除本地临时表,然后重建同样名字的临时表就可以了吗? 事实告诉我们,虽然上面sql可以找到当前会话创建的本地临时表删除,但是如果是在存储过程里面,使用这种方式,创建本地临时表,然后删除、创建,依然会遇到这个错误,如下测试所示:

 

 

 

create procedure prc_test
as
begin 
 
    if object_id('tempdb..#temp_test' ) is not null drop table #temp_test;
    create table #temp_test( id int, name varchar(32));
 
    insert into #temp_test values(10, 'jimmy');
 
    if object_id('tempdb..#temp_test' ) is not null drop table #temp_test;
 
    create table #temp_test( id int, name varchar(32));
 
    insert into #temp_test values(100, 'kerry');
 
 
    drop table #temp_test;
end
go

 

 

 

 

其实在一个批处理里面执行下面sql语句也会报错,但是改写一下sql,在删除后面加上一个go语句,那么这样是不会报错的(但是你单个sql,一条一条执行是不会报错的).

 

 

    create table #temp_test( id int, name varchar(32));

 

    insert into #temp_test values(10, ‘jimmy’);

 

    if object_id(‘tempdb..#temp_test’ ) is not null drop table #temp_test;

 

    create table #temp_test( id int, name varchar(32));

 

    insert into #temp_test values(100, ‘kerry’);

 

 

    drop table #temp_test;

 

 

 

 

 

至于原因是什么呢?网上有种分析是因为解析错误(parse error),这里我也倾向于这种说法,因为测试过程中,发现其实上面sql语句报错,但是实质上,本地临时表已经在tempdb被删除了。加上一个go这种改写方法,其实使用两个批处理,下面这样的sql是不会报错的。

 

 

    create table #temp_test( id int, name varchar(32));

 

    insert into #temp_test values(10, ‘jimmy’);

 

    if object_id(‘tempdb..#temp_test’ ) is not null drop table #temp_test;

 

    go

 

    create table #temp_test( id int, name varchar(32));

 

    insert into #temp_test values(100, ‘kerry’);<

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐