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_%'
_____________
Code for TallyGenerator