SQL error table name using [,] and '

Question

I have some problems/questions with the usage of [,] and ' in table names.
For example why this working

SELECT TOP 1000 [O'test]
      ,[NumeNOU]
  FROM [dbname].[dbo].[O'conor]

and this not

use dbname
GO
DECLARE @COUNT int
SELECT @COUNT = Count(*) FROM  dbname.INFORMATION_SCHEMA.COLUMNS  where TABLE_NAME = [dbname].[dbo].[O'conor]
PRINT @COUNT

with the error

Msg 4104, Level 16, State 1, Line 4 The multi-part identifier "dbname.dbo.O'conor" could not be bound.

or this

use dbname
    GO
    DECLARE @COUNT int
    SELECT @COUNT = Count(*) FROM  dbname.INFORMATION_SCHEMA.COLUMNS  where TABLE_NAME = [O'conor]
    PRINT @COUNT

with the error

Msg 207, Level 16, State 1, Line 4 Invalid column name 'O''conor'.

but is working like this

use dbname
        GO
        DECLARE @COUNT int
        SELECT @COUNT = Count(*) FROM  dbname.INFORMATION_SCHEMA.COLUMNS  where TABLE_NAME = 'O''conor'
        PRINT @COUNT

TABLE_NAME = [dbname].[dbo].[O'conor] This one needs to surrounded by quotes e.g. '[dbname].[dbo].[O'conor]' — Alex, Apr 04 '16 at 07:43
The one with `WHERE TABLE_NAME = [O'conor]` is definitely incorrect because you're trying to use it as a column name, you would instead do `WHERE TABLE_NAME = 'O''conor'` — Lasse V. Karlsen, Apr 04 '16 at 07:43
The error messages are entirely correct, those tables does not have a column with that name. You might want to look for a row where the table name is that but then you need to use a string literal, and not reference a column that does not exist. So what is the question? You say "this does not work, but this does". Is the question "why?" ? — Lasse V. Karlsen, Apr 04 '16 at 07:45
The question was why in the first case it worked and in those 2 it didn't. I am not specifying anywhere the column name . Somebody posted a good answer but deleted it :( — RobertP, Apr 04 '16 at 07:59

score 0 · Accepted Answer · edited May 23 '17 at 12:31

0

1 + 2

use dbname
GO
DECLARE @COUNT int
SELECT @COUNT = Count(*) FROM  dbname.INFORMATION_SCHEMA.COLUMNS  where TABLE_NAME = [dbname].[dbo].[O'conor]
PRINT @COUNT

use dbname
GO
DECLARE @COUNT int
SELECT @COUNT = Count(*) FROM  dbname.INFORMATION_SCHEMA.COLUMNS  where TABLE_NAME = [O'conor]
PRINT @COUNT

Thess queries will not work since TABLE_NAME expects a sysname value but you supply an identifier.

3

use dbname
GO
DECLARE @COUNT int
SELECT @COUNT = Count(*) FROM  dbname.INFORMATION_SCHEMA.COLUMNS  where TABLE_NAME = 'O''conor'
PRINT @COUNT

This query works since you are supplying a sysname value.

Side note: It's better to use object names that contains only letters, numbers and underscores, that way you don't need to use the square brackets around them.

edited May 23 '17 at 12:31

Community

1
1

answered Apr 04 '16 at 07:58

Zohar Peled

79,642
10
69
121

Thank you very much for your answer – RobertP Apr 04 '16 at 08:03
[Glad to help :-)](http://meta.stackoverflow.com/questions/291325/how-to-show-appreciation-to-a-user-on-stackoverflow/291327#291327) – Zohar Peled Apr 04 '16 at 08:07

SQL error table name using [,] and '

1 Answers1