WITH ColumnStoreIndexes AS (
SELECT
.[object_id]
, IndexName = .[name]
FROM
[sys].[indexes] AS
WHERE
.[type] IN (5,6) -- columnstore indexes
), NonClusteredIndex AS (

SELECT
.[object_id]
, IndexName...

Can you also post the query fo rrequirement 2 
Identify all the tables which contain both columnsstoreindex and non-clustered index.

for requirement 2.  i am doing below . 

select distinct ss.name , object_name(st.object_id) , * from sys.indexes si
INNER JOIN sys.tables st on st.object_id = si.object_id
INNER JOIN sys.schemas ss...

Just to reiterate the whole discussion below is what I want to achieve.

1. Identify all tables should have more than one million records and doesn't have any columnstoreindex on it.
2. Identify...

even after including the group by it is not giving the right result.

SELECT distinct
  ss.name,
  object_name(sp.object_id)
,  rows AS NoofRows
FROM sys.partitions sp
INNER JOIN sys.tables st on st.object_id = sp.object_id
INNER JOIN sys.schemas ss on ss.schema_id= st.schema_id

SELECT distinct
  object_name(sp.object_id)
,  rows AS NoofRows
FROM sys.partitions sp
INNER JOIN sys.tables st on st.object_id = sp.object_id
WHERE index_id NOT IN (5) -- clustered index or...

I have tried but no luck.

But nobody replied

I am basically a Teradata developer with no knowledge of SQL but this report is really critical to other teams.

Please show some mercy on me, I have to complete this, I have only 30mins from now to share this report.

Please i am really a new bee.

Can you please share the complete query instead of just half ?

I am not getting. Please help

DECLARE @TableRowCounts TABLE ([TableName] VARCHAR(128), [RowCount] INT) ;
INSERT INTO @TableRowCounts ([TableName], [RowCount])
EXEC sp_MSforeachtable 'SELECT ''?'' [TableName],COUNT(*) [RowCount] FROM ?' ;
SELECT...