• What are you trying to count? If all the rows in the table, then you can query sys.partitions or sys.dm_db_partition_stats (sysindexes is deprecated and should not be used). If you're trying to count a subset of the table, then you'll need to use COUNT(*)

    If you're trying to count the rows selected from the CTE, try @@RowCount, though that CTE is going to perform terribly, count or no count. A CTE is not a table and hence doesn't appear in any of the catalog views.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass