Tim

1. I had to alter the following sql to use alias' rather than cut'n'paste the database.table info (eg TC.TableName rather than Information.TableColumns.TableName) otherwise it didn't run at all.

2. I'm obviously doing something wrong cos it gives me 5200 rows with many duplicates. What I'm looking for is a list of all columns in the table, their type, collation and if part of an index what type it is. Ideally I'd like the last column just to have something in for the Text indices.

SELECT  TC.Name, TC.Type, TC.Collation , X.Type

FROM  Information.TableColumns TC

 join information.indexes X
  on (TC.TableName = X.TableName)
 join information.indexcolumns XC
  on
     (X.TableName = TC.TableName)
      and
     (X.Name = XC.IndexName)

WHERE  TC.TableName = 'MandN'


Roy Lambert


================================================================================
SQL Query (Executed by ElevateDB 1.02 Build 1)

Note: The SQL  shown here is generated by ElevateDB and may not be exactly the
same as the SQL that was originally entered.  However, none of the differences
alter the execution results in any way.

================================================================================

SELECT ALL
"TC"."Name" AS "Name",
"TC"."Type" AS "Type",
"TC"."Collation" AS "Collation",
"X"."Type" AS "Type1"
FROM "Information"."TableColumns" AS "TC" INNER JOIN "information"."indexes" AS
"X" ON ("TC"."TableName" = "X"."TableName"),
INNER JOIN "information"."indexcolumns" AS "XC" ON ("X"."TableName" =
"TC"."TableName") AND ("X"."Name" = "XC"."IndexName")
WHERE "TC"."TableName" = 'MandN'

Source Tables
-------------

TableColumns (TC): 173 rows
indexes (X): 44 rows
indexcolumns (XC): 53 rows

Result Set
----------

The result set was static
The result set consisted of zero or more rows

Filtering
---------

The following filter condition was applied to the TableColumns table:

"TC"."TableName" = 'MandN' [Index scan: 25 keys, 4096 bytes estimated cost]

Joins
-----

The driver table was the TableColumns (TC) table

The TableColumns (TC) table was joined to the indexes (X) table with the inner
join expression:

("TC"."TableName" = "X"."TableName")

The TableColumns (TC) table was joined to the indexcolumns (XC) table with the
inner join expression:

("X"."TableName" = "TC"."TableName") AND ("X"."Name" = "XC"."IndexName")

The optimizer attempted to re-order the joins to a more optimal order

The joins were already in the most optimal order

The following join condition was applied to the indexes (X) table:

("X"."TableName" = "TC"."TableName" [Index scan])

The following join condition was applied to the indexcolumns (XC) table:

("XC"."IndexName" = "X"."Name" [Index scan]) AND ("X"."TableName" =
"TC"."TableName" )

================================================================================
5200 row(s) returned in 0.203 secs
================================================================================

Roy,

<< 1. I had to alter the following sql to use alias' rather than cut'n'paste
the database.table info (eg TC.TableName rather than
Information.TableColumns.TableName) otherwise it didn't run at all. >>

You don't need schema names for column references, just qualify the column
with the table name or table correlation name, and you'll be all set.
Schema names are only used with table references.

<< 2. I'm obviously doing something wrong cos it gives me 5200 rows with
many duplicates. What I'm looking for is a list of all columns in the table,
their type, collation and if part of an index what type it is. Ideally I'd
like the last column just to have something in for the Text indices. >>

This should be what you want:

SELECT TC.Name, TC.Type, TC.Collation, X.Type
FROM Information.TableColumns TC
join information.indexcolumns XC on (XC.TableName = TC.TableName) and
(XC.ColumnName=TC.Name)
join information.indexes X on (TC.TableName = X.TableName) and (X.Name =
XC.IndexName)
WHERE TC.TableName = 'MandN'
GROUP BY TC.Name, TC.Type, TC.Collation, X.Type

To only see text indexes, use this:

SELECT TC.Name, TC.Type, TC.Collation, X.Type
FROM Information.TableColumns TC
join information.indexcolumns XC on (XC.TableName = TC.TableName) and
(XC.ColumnName=TC.Name)
join information.indexes X on (TC.TableName = X.TableName) and (X.Name =
XC.IndexName)
WHERE TC.TableName = 'MandN' AND X.Type='Text'

GROUP BY TC.Name, TC.Type, TC.Collation, X.Type

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Interesting try, and thanks, but not what I was trying to get. The table has 35 columns and I was hoping to get a list of those showing name, type, collation and if it has a text index defined.

Roy Lambert

Tim


I think I've cracked it.

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

Gives me what I want, but is it safe to assume it will always work?

Roy Lambert

Roy,

<< I think I've cracked it.

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

Gives me what I want, but is it safe to assume it will always work? >>

Sure, why wouldn't it ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


The thing that surprised me was the group by gave me just the Text indices or blank and I don't know why


Roy Lambert

Roy,

<< The thing that surprised me was the group by gave me just the Text
indices or blank and I don't know why >>

Ahh yes, you need to group on more than just the Name column if you want to
see more than just one row per column.  You should ideally be grouping on:

name,type,collation,XC.IndexName

at least.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Yup as you did in your sample code but I only want one row. All I wanted was a list of all the columns and if they happened to have a text index assigned so I can allow full text searching. Its your fault really since CONTAINS no longer allows me to use it on columns that don't have a text index.

Roy Lambert

Roy,

<< Yup as you did in your sample code but I only want one row. All I wanted
was a list of all the columns and if they happened to have a text index
assigned so I can allow full text searching. Its your fault really since
CONTAINS no longer allows me to use it on columns that don't have a text
index. >>

Well, the DBISAM was was a little illogical since the whole purpose of using
TextSearch was for *indexed* text searching.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Hmm. My take was that TEXTSEARCH was for finding words in a "text" column indexing simply made it faster. I don't know about under the hood but for me it was a lot better than writing a wadge of LIKE '%xxx%' if you wanted more than one word.

Roy Lambert