I want to retrieve the table names which are not clustered columnstored as well as the database that these tables are from (I have many databases).

In other words, I would like

SELECT t.name
FROM sys.indexes i
JOIN sys.tables t on i.object_id = t.object_id
WHERE i.type <> 5

but for all the databases that I have access to and to return the information in a single query.

I realise that the ideal way to do this is through a stored procedure. However, I am not too familiar with writing stored procedures, so I have tried to find some help online and ended up with the following:

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = (SELECT ' UNION ALL
                SELECT ' +  + QUOTENAME(name,'''') + ' AS database_name,
                                        s.name COLLATE DATABASE_DEFAULT AS schema_name,
                                        t.name COLLATE DATABASE_DEFAULT AS table_name,
                                        i.type AS table_type
                                    FROM '+ QUOTENAME(name) + '.sys.tables t
                                    JOIN '+ QUOTENAME(name) + '.sys.schemas s on s.schema_id = t.schema_id
                                    JOIN '+ QUOTENAME(name) + '.sys.indexes i on i.object_id = t.object_id'
                FROM sys.databases 
                WHERE state = 0 AND name NOT IN ('DatabaseSizes', 'master', 'model', 'msdb', 'SSISDB', 'tempdb') AND HAS_DBACCESS(name) = 1
                FOR xml path(''), type).value('.', 'NVARCHAR(MAX)');

SET @sql = STUFF(@sql, 1, 12, '') + ' ORDER BY database_name, schema_name, table_name';

EXEC (@sql);

However, the only problem now is that this returns me every table in every database that is online and that I have access to when, in fact, I only want those with [table_type] <> 5 and I also don’t really need the [table_type] column to be shown.

When I try something like

DECLARE @sql NVARCHAR(MAX);

SELECT @sql = (SELECT ' UNION ALL
                SELECT ' +  + QUOTENAME(name,'''') + ' AS database_name,
                                        s.name COLLATE DATABASE_DEFAULT AS schema_name,
                                        t.name COLLATE DATABASE_DEFAULT AS table_name
                                    FROM '+ QUOTENAME(name) + '.sys.tables t
                                    JOIN '+ QUOTENAME(name) + '.sys.schemas s on s.schema_id = t.schema_id
                                    JOIN '+ QUOTENAME(name) + '.sys.indexes i on i.object_id = t.object_id'
                FROM sys.databases 
                WHERE state = 0 AND name NOT IN ('DatabaseSizes', 'master', 'model', 'msdb', 'SSISDB', 'tempdb') AND HAS_DBACCESS(name) = 1 AND type <> 5
                FOR xml path(''), type).value('.', 'NVARCHAR(MAX)');

SET @sql = STUFF(@sql, 1, 12, '') + ' ORDER BY database_name, schema_name, table_name';

EXEC (@sql);

where I put the required condition into the WHERE condition, the procedure will not run as SQL says that type is an invalid column.

Is it a syntax issue? If so, then what should be the correct syntax?

Any solutions will be greatly appreciated!