How to tell if table column name was created with quotes ("columnname") in Snowflake?

Some of the columns in our Snowflake tables were created with quotes. I need to determine which were created with quotes and which ones were not.

I looked at INFORMATION_SCHEMA.COLUMNS, but I cannot tell any difference from here.

Where can I query to determine the difference?

CREATE OR REPLACE TABLE TestSchemaTable1 (
    COLUMN1 TEXT,
    "column8" TEXT
);

Identifier Requirements

> Unquoted object identifiers:
>
> - Start with a letter (A-Z, a-z) or an underscore (“_”).
>
> - Contain only letters, underscores, decimal digits (0-9), and dollar signs (“$”).
>
> - Are stored and resolved as uppercase characters (e.g. id is stored and resolved as ID).
>
> If you put double quotes around an identifier (e.g. “My identifier
> with blanks and punctuation.”), the following rules apply:
>
> - The case of the identifier is preserved when storing and resolving the identifier (e.g. "id" is stored and resolved as id).
>
> - The identifier can contain and start with ASCII, extended ASCII, and non-ASCII characters.

It is possible to retrieve information about quoting by analyzing query profile SYSTEM$EXPLAIN_PLAN_JSON:

SET table_name = 'TestSchemaTable1';


SELECT REPLACE(s.value::TEXT, '"s~~".') AS column_name
      ,column_name RLIKE '^".*"$' AS is_quoted
FROM (
 SELECT SYSTEM$EXPLAIN_PLAN_JSON('SELECT * FROM '||$table_name||' AS "s~~" LIMIT 0')
) AS cte(c)
,TABLE(FLATTEN(INPUT=>PARSE_JSON(cte.c), PATH=>'Operations[0][0].expressions')) AS s;

For input table:

CREATE OR REPLACE TABLE TestSchemaTable1 (
    COLUMN1 TEXT,
    column2 TEXT,
    COLumn3 TEXT,
    "COLumn4" TEXT,
    "column8" TEXT,
    "COLUM-9" TEXT
);

Output:

EDIT Approach 2:

Using the regexp pattern from Snowpark/_internal/utils:

SNOWFLAKE_CASE_INSENSITIVE_QUOTED_ID_PATTERN = r'("([A-Z_][A-Z0-9_$]{0,255})")'

Full query:

SELECT COLUMN_NAME, COLUMN_NAME NOT RLIKE '([A-Z_][A-Z0-9_$]{0,255})' AS IS_QUOTED
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME ILIKE 'TestSchemaTable1'
ORDER BY ORDINAL_POSITION;

/*
COLUMN_NAME	IS_QUOTED
COLUMN1	FALSE
COLUMN2	FALSE
COLUMN3	FALSE
COLumn4	TRUE
column8	TRUE
COLUM-9	TRUE
*/

It doesn't really matter if you created column names with quotes or not, UNLESS the column names are not all uppercase OR the column name include other symbols that are not underscores or dollar signs. Snowflake is case-insensitive unless there are double-quotes AND there are lowercase letters in those double-quotes.

If you use double quotes and they are all uppercase then that column name will still be case-insensitive.

I believe you are truly asking, which column names are case-sensitive OR if there are non-dollar sign or underscore symbols.

Here is an example:

CREATE OR REPLACE TABLE test_names (
    lower_case_no_quotes VARCHAR,
    UPPER_CASE_NO_QUOTES VARCHAR,
    MixedCaseNoQuotes VARCHAR,
    "lower_case_quotes" VARCHAR,
    "UPPER_CASE_QUOTES" VARCHAR,
    "MixedCaseQuotes" VARCHAR,
    "column8" VARCHAR,
    "COLUMN-9" VARCHAR,
    "SYMBOL$#COLUMN" VARCHAR,
    OTHERCOLUMN1234 VARCHAR,
    dollar$ign VARCHAR,
    "WHITE SPACE" VARCHAR
);

To be able to query that table you need to include quotes on some of those columns but not all:

SELECT 
     lower_case_no_quotes
    ,upper_case_no_quotes
    ,mixedcasenoquotes
    ,"lower_case_quotes" -- You need to include quotes
    ,upper_case_quotes -- No quotes needed since it was fully uppercase when created
    ,"MixedCaseQuotes" -- You need to include quotes
    ,"column8" -- You need to include quotes
    ,"COLUMN-9" -- Needs Quotes
    ,"SYMBOL$#COLUMN" -- Needs Quotes
    ,othercolumn1234 -- Doesn't need quotes
    ,dollar$ign -- Doesn't need quotes
    ,"WHITE SPACE"
FROM test_names
;

You can find a list of all case-sensitive columns from the information schema using a query like this:

SELECT *
FROM information_schema.columns
WHERE column_name != UPPER(column_name)
   OR regexp_like(column_name, '.*[^a-zA-Z0-9$_].*')
;

In the example above, you would only see 6 columns return:

• lower_case_quotes
• MixedCaseQuotes
• column8
• COLUMN-9
• SYMBOL$#COLUMN
• WHITE SPACE