Great stuff. Thanks.

Only 2 notes:

WHERE <...> AND i.indid < 255 and i.name NOT LIKE '_WA_Sys_%'

eliminates NULLs and makes join to sysindexes actually INNER, not LEFT.

I'm not sure if it's possible to have a table without indexes at all. It must be an index with indid either 0 or 1 for each table.

And COALESCE or ISNULL works faster than case ... IS NULL then ... else ... end.

So I have rewritten your code this way:

SELECT o.id AS TableID ,u.name Owner,o.name TableName,

  i.Indid AS IndexID

  , CASE i.name

   WHEN o.name THEN '** NONE **'

  ELSE i.name END AS IndexName,

       CASE i.indid

   WHEN 1 THEN 'CLUSTERED'

   WHEN 0 THEN 'HEAP'

  ELSE 'NONCLUSTERED' END AS ClusterType,

  CASE

     WHEN (i.status & 2048) > 0 THEN 'PRIMARY KEY'

   WHEN (i.status & (2|4096)) > 0 THEN 'UNIQUE'

  ELSE '' END AS UniqueType,

    CASE      

   WHEN (i.status & (2048)) > 0

     OR ((i.status & (4096)) > 0 )

    THEN 'CONSTRAINT'

   WHEN i.indid = 0 THEN ' '

  ELSE 'INDEX' END AS IndexType,

-- This following part is non essential

-- It is a pre char aggregate I use in other scripts

-- to generate create and drop scripts

 COALESCE (     INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 1), '') +

    COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 2), '') +

 COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 3), '') +

 COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 4), '') +

 COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 5), '') +

    COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 6), '') +

    COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 7), '') +

    COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 8), '') +

    COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 9), '') +

    COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 10), '') +

    COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 11), '') +

    COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 12), '') +

    COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 13), '') +

    COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 14), '') +

    COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 15), '') +

    COALESCE (', '+INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 16), '') AS AllColName,

--

     COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 1), '')  AS ColName1,

     COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 2), '') AS ColName2,

  COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 3), '') AS ColName3,

     COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 4), '') AS ColName4,

     COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 5), '') AS ColName5,

     COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 6), '') AS ColName6,

     COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 7), '') AS ColName7,

     COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 8), '') AS ColName8,

     COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 9), '') AS ColName9,

     COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 10), '') AS ColName10,

  COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 11), '') AS ColName11,

     COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 12), '') AS ColName12,

     COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 13), '') AS ColName13,

     COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 14), '') AS ColName14,

     COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 15), '') AS ColName15,

     COALESCE (INDEX_COL(u.name+'.'+ QUOTENAME(o.name), i.indid, 16), '') AS ColName16

 FROM sysobjects o (NOLOCK)

 INNER JOIN sysindexes i (NOLOCK) ON o.id = i.id

    JOIN sysusers u (NOLOCK) ON o.uid = u.uid

 WHERE o.type = 'U' AND i.indid < 255

 AND o.name NOT IN ('dtproperties')

 AND i.name NOT LIKE '_WA_Sys_%'