Confusing behaviour - SQL Server Information Schema and columns created by using the wizard

Question

EDIT: I tried this code, part of what a user mentioned, and it returns the fields correctly.

SELECT * FROM INFORMATION_SCHEMA.TABLES 
        JOIN INFORMATION_SCHEMA.COLUMNS on 
        INFORMATION_SCHEMA.TABLES.TABLE_NAME = 
        INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
WHERE INFORMATION_SCHEMA.TABLES.TABLE_NAME = 'myTable'

There is something in the former query that works with some databases but not with others

---

EDIT2:

I've tried setting up a Profiler trace to check the columns creation through the wizard, as a user suggested. It is using the same schema, apparently.

ALTER TABLE dbo.table1 ADD
field2 nchar(10) NULL

---

I'm working with SQL Server 2014, and created databases by using the wizard, I mean, not by code, but by using the UI.

Then, I tried to fetch metadata information as regards their tables' columns.. and no results are returned. I query INFORMATION_SCHEMA.

I've tried using the same query for databases I've created by code, and it works fine, it returns the columns and their values, etc.

I've tried looking it up over the internet, but can't seem to think anything that fits cases like these. I think it's a bit weird that it makes a difference with columns created by code and through the UI.. If anyone knows why something like this can happen, or seems familiar, I would appreciate some light on it :)

Here's the code I use to retrieve the columns' metadata:

USE 'database'
SELECT infSch.TABLE_CATALOG,
       infSch.TABLE_NAME,
       sysCols.name,
       infSch.ORDINAL_POSITION,
       sysCols.is_nullable,
       infSch.DATA_TYPE,
       infSch.CHARACTER_MAXIMUM_LENGTH,
       sysCols.is_identity,
       IIF(infSchCons.CONSTRAINT_TYPE = 'PRIMARY KEY', 1, 0),
       IIF(infSchCons.CONSTRAINT_TYPE = 'FOREIGN KEY', 1, 0),
       IIF(infSchCons.CONSTRAINT_TYPE = 'UNIQUE', 1, 0),
       IIF(infSchCons.CONSTRAINT_TYPE = 'CHECK', 1, 0)
FROM sys.columns as sysCols 
RIGHT JOIN INFORMATION_SCHEMA.COLUMNS as infSch on sysCols.name = 
     infSch.COLUMN_NAME
RIGHT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS as infSchCons on 
     infSch.TABLE_NAME = infSchCons.TABLE_NAME

Results:

• If table and columns were created by code --> it returns all the columns and its metadata, for a given database

• If table and columns were created by using the UI and windows --> it returns nothing, just an empty set of results

Answer 1

Right joins change the nature of the query, and they can always be re-written by reversing the order of tables. Avoid the right joins.... try this instead:

SELECT infSch.TABLE_CATALOG,
       infSch.TABLE_NAME,
       sysCols.name,
       infSch.ORDINAL_POSITION,
       sysCols.is_nullable,
       infSch.DATA_TYPE,
       infSch.CHARACTER_MAXIMUM_LENGTH,
       sysCols.is_identity,
       IIF(infSchCons.CONSTRAINT_TYPE = 'PRIMARY KEY', 1, 0),
       IIF(infSchCons.CONSTRAINT_TYPE = 'FOREIGN KEY', 1, 0),
       IIF(infSchCons.CONSTRAINT_TYPE = 'UNIQUE', 1, 0),
       IIF(infSchCons.CONSTRAINT_TYPE = 'CHECK', 1, 0)
FROM sys.columns as sysCols 
LEFT JOIN INFORMATION_SCHEMA.COLUMNS as infSch on sysCols.name = 
     infSch.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS as infSchCons on 
     infSch.TABLE_NAME = infSchCons.TABLE_NAME

That query does NOT limit results to only those where constraints exist.

Try comparing these:

select count(*) from (

    SELECT
        infSchCons.*
    FROM sys.columns as sysCols 
    right JOIN INFORMATION_SCHEMA.COLUMNS as infSch on sysCols.name = infSch.COLUMN_NAME
    right JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS as infSchCons on infSch.TABLE_NAME = infSchCons.TABLE_NAME

    ) x
;

select count(*) from (

    SELECT
        infSchCons.*
    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS infSchCons
    left JOIN INFORMATION_SCHEMA.COLUMNS AS infSch ON infSchCons.TABLE_NAME = infSch.TABLE_NAME
    left JOIN sys.columns AS sysCols ON infSch.COLUMN_NAME = sysCols.name

    ) x
;

The last query is a re-write of the original from clause, and here it is easier (in my view) to see that you must have constraints for any row to be returned.

---

EDIT

The question asked why a particular query did not work in some databases.

That query uses RIGHT OUTER JOINS

Due to the way that join tyupe operates it alters the priority of tables - and this can be confusing

Because all right joins can be "reversed", when you do the the equivalent query (to the original) has a from clause like this:

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS infSchCons
left JOIN INFORMATION_SCHEMA.COLUMNS AS infSch ON infSchCons.TABLE_NAME = infSch.TABLE_NAME
left JOIN sys.columns AS sysCols ON infSch.COLUMN_NAME = sysCols.name

So the table with the highest priority is INFORMATION_SCHEMA.TABLE_CONSTRAINTS and if there are no rows in that table, the query will not return any data.

In the second edit to the question there is a query that works:

SELECT
    *
FROM INFORMATION_SCHEMA.TABLES
JOIN INFORMATION_SCHEMA.COLUMNS ON INFORMATION_SCHEMA.TABLES.TABLE_NAME =
    INFORMATION_SCHEMA.COLUMNS.TABLE_NAME
WHERE INFORMATION_SCHEMA.TABLES.TABLE_NAME = 'myTable'

And: this "works" because the table with the highest priority is INFORMATION_SCHEMA.TABLES which is almost guaranteed to have rows in it.

So, the original query uses the wrong highest priority table, which is obscured due to the right joins. The query that works uses a sensible table as its base table, and does not use right joins.