I'm unable to delete the table, as it shows the error below:

Drop failed for Table "dbo.CustomerTest"

Please advise what should I do to delete this table?

CodePudding user response：

There is lock happening on the table and you can find out the session locking the table using sp_lock.

Here, We are trying to mimic lock scenario.

SELECT * FROM dbo.test(a INT) 

insert into test values(1),(2);

BEGIN TRANSACTION

update TEST set a = 3 where a =1

Now, if we call sp_lock in another session, we will come to know the session, which is apply lock.

declare @LOCKS table(
spid smallint, dbid smallint, objid int, indid smallint, type nchar(4), resource nchar(32),
mode nvarchar(8), status nvarchar(5))

insert into @locks
exec sp_lock
select spid, db_name(dbid) as databasename, OBJECT_NAME(objid) as tablename,type,resource,mode, status from @locks

If you want to kill the session, you have to kill it accordingly.

kill 60 -- session holding lock on the table