SQL Server Table Missing – A Case of a Missing Table Name

Hi Guys –  here’s a quickie

Recently, I came across a scenario where in I was informed that a table doesn’t has a name; yes, the sys.objects table returned every other detail of the table, however the name was blank. It came as a surprise to me as I never came across this kind of situation.

I myself checked and found that it was indeed the case of SQL Server Table Missing.  I didn’t have any idea of how to replicate the case and then I decided to rename the table.  There was a problem in renaming the table and it was which value I should pass as old name parameter in sp_rename stored procedure. I first tried this query sp_rename ‘dbo.’,’newtable’ and got below error

Msg 15253, Level 11, State 1, Procedure sp_rename, Line 107

Syntax error parsing SQL identifier ‘dbo.’.

Then I tried sp_rename ‘dbo. ‘,’newtable’ and I was then able to successfully rename the table to newtable.

Surprised I was;   sql server allows table to be created with a space. However this doesn’t ends here.

   

I tried creating a table with double space instead of single but it terminated with below error.

Msg 2714, Level 16, State 6, Line 1

There is already an object named ‘  ‘ in the database.

I shared the case with Sarab to check whether he came across this situation or not only to found that he too was equally surprised.

One more thing. how would you query such table 🙂

select * from [ ]

To conclude,  sql server allows a table to be created with a space but not will multiple spaces.

 
Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

   

3 Comments on “SQL Server Table Missing – A Case of a Missing Table Name”

  1. Hi Ahmad,

    Yeah Nice one.
    Just one more thing to add up here in the Abnornal\Unexpected behaviour of SQL Server:

    When we do a query like:
    Select * from dbo.[ ]
    Yes this is what you have told, where we can see the table with enteries.

    But even if i do the query like:
    Select * from dbo.[ ]
    Where the table name space is >=1, then also will get the SAME result as an output.

  2. Yes Piyush, it considers single/multiple space as one table and thus it doesn’t allows you to create a table with multiple spaces.

    Thanks,
    Ahmad

Leave a Reply

Your email address will not be published.