1

I've dropped a table but it is still listed in sys.tables. What's more confusing is that I can create the table again as one user, but if I log in as another user with the same permissions, I can't (I get a message that the table already exists). I need to recreate the table as this second user.

I've managed to log in as the sa user and run DBCC checkdb, but it hasn't done anything to resolve the problem. How can I remove the record in sys.tables permanently (I'm assuming this is the cause of the problem).

6
  • Please post the code you are using to CREATE/DROP and the relevant output of sys.tables. Sounds like it might just be in a different schema. Commented Nov 19, 2013 at 12:28
  • from sqlcmd command line: USE DATABASE 'DBName' GO DROP TABLE 'TableName' GO Commented Nov 19, 2013 at 12:36
  • Have you tried being explicit with your schema naming? Don't relying on defaults. At least to rule out schema confusion (as pointed out by @MartinSmith). Commented Nov 19, 2013 at 13:00
  • It's not that. I believe it's something to do with this (from the MSDN documentation from DROP TABLE: "DROP TABLE and CREATE TABLE should not be executed on the same table in the same batch. Otherwise an unexpected error may occur.") Unfortunately there is a script which does this in our application, and I'd like to know if this is the outcome and what I can do to fix it. Commented Nov 19, 2013 at 14:08

2 Answers 2

1

Whether using dbo or not, always specify the schema prefix explicitly. Otherwise, if you have users with different default schemas, you could end up with objects in a schema you didn't expect. One user creates a table in one schema, and the other can't see it. Here is a quick example:

CREATE SCHEMA floob AUTHORIZATION dbo;
GO

CREATE USER blat1 
  WITHOUT LOGIN WITH DEFAULT_SCHEMA = floob;

CREATE USER blat2 
  WITHOUT LOGIN WITH DEFAULT_SCHEMA = dbo;

GRANT CREATE TABLE TO blat1, blat2;
GRANT ALTER ON SCHEMA::floob TO blat1, blat2;
GRANT ALTER ON SCHEMA::dbo TO blat1, blat2;

EXECUTE AS USER = 'blat1';
GO
CREATE TABLE Employees(ID INT);
GO
REVERT;

SELECT s.name, t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE t.name = N'Employees';

EXECUTE AS USER = 'blat2';
GO
CREATE TABLE Employees(ID INT);
GO
REVERT;

SELECT s.name, t.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
WHERE t.name = N'Employees';

Results:

-----  ---------
floob  Employees

-----  ---------
dbo    Employees
floob  Employees
0

You should check the default schema of the user through which you are deleting the table. The table existing should be in another schema and the table being deleted is in the default schema of the user.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.