I'm still trying to get my info from the information database and I'm begining to wonder if OUTER joins aren't working.

select name,type,collation
from information.tablecolumns TC
WHERE tablename = 'MandN'

gives 25 rows

select name,type,collation,XC.IndexName
from information.tablecolumns TC
right outer join information.IndexColumns XC ON XC.ColumnName = TC.Name
WHERE TC.tablename = 'MandN'
AND
XC.TableName = 'MandN'

only gives 22 rows

I've tried left outer join, right outer join, join, left join, join, offering it a cup of coffee and in the immortal words of Catweasel "nothing works"

My understanding of sql isn't good so will someone please put me out of my misery.

Roy Lambert

Roy,

<< only gives 22 rows >>

It's because of the WHERE clause - it's filtering out any NULL values in the
TableName columns.  Try this instead:

select name,type,collation,XC.IndexName
from information.tablecolumns TC
left outer join information.IndexColumns XC ON XC.ColumnName = TC.Name AND
XC.TableName = 'MandN'
WHERE TC.tablename = 'MandN'

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

Thanks


Roy Lambert