3

Can anyone provide me a query to select the list of all tables in a db that doesn't have their names in uppercase?

Srivastava
  • 3,500
  • 12
  • 46
  • 74

5 Answers5

5
SELECT * FROM
sys.tables -- or local equivalent. This is for SQL Server
WHERE
   UPPER(name) COLLATE Latin1_General_BIN <> name COLLATE Latin1_General_BIN
gbn
  • 422,506
  • 82
  • 585
  • 676
  • @Srivastava - have you considered providing a little more detail than "it's not working"? Is it giving you results you know are incorrect, is it giving an error message (that you could tell people trying to help you)? – Damien_The_Unbeliever Nov 25 '10 at 13:41
  • yes it is pointing error lile this Msg 208, Level 16, State 1, Line 1 Invalid object name 'sys.tables'. – Srivastava Nov 25 '10 at 13:48
  • 2
    @Srivastava - OK, so you're using SQL Server 2000 (or earlier), so you'll need to use `sysobjects` instead of `sys.tables`, and add ` AND OBJECTPROPERTY(ID,N'IsTable')=1 and OBJECTPROPERTY(ID,N'IsMSShipped')=0` to the where clause. – Damien_The_Unbeliever Nov 25 '10 at 14:04
2

@gbn's answer is correct for any up to date version of SQL Server.

For 2000:

SELECT * FROM
sysobjects
WHERE
   UPPER(name) COLLATE Latin1_General_BIN <> name COLLATE Latin1_General_BIN
AND OBJECTPROPERTY(ID,N'IsTable')=1 and OBJECTPROPERTY(ID,N'IsMSShipped')=0
Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
0

Table names in select statements may be case insensitive, but that doesn't mean you can't return names of tables that are not uppercase:

SELECT  name
FROM    sys.tables
WHERE   (name <> UPPER(name))
Carvellis
  • 3,992
  • 2
  • 34
  • 66
0
SELECT name
FROM sys.tables
WHERE name <> UPPER(name) COLLATE Latin1_General_BIN
Anthony Faull
  • 17,549
  • 5
  • 55
  • 73
0
SELECT name
FROM sys.tables 
WHERE CAST(name as varbinary) <> CAST(UPPER(name) as varbinary)
Michael Riley - AKA Gunny
  • 5,074
  • 4
  • 42
  • 89