Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question
    
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. –  Martin Smith Nov 19 '13 at 12:28
    
from sqlcmd command line: USE DATABASE 'DBName' GO DROP TABLE 'TableName' GO –  Chris Cooper Nov 19 '13 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). –  Thomas Stringer Nov 19 '13 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. –  Chris Cooper Nov 19 '13 at 14:08
    

2 Answers 2

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
share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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