Can't assign an alias to result of a join - SQL

Question

I have a DB with lots of tables without use. I'd like to filter out the tables without any data. So I used a snippet How to fetch the row count for all tables by @ismetAlkan.

However, I want to filter out 0, so used something like this and it doesn't work.

USE  [my_db]
GO
SELECT * FROM
(
SELECT      SCHEMA_NAME(A.schema_id) + '.' +
        A.Name, SUM(B.rows) AS 'RowCount'  
FROM        sys.objects A
INNER JOIN sys.partitions B ON A.object_id = B.object_id 
WHERE       A.type = 'U' 
GROUP BY    A.schema_id, A.Name
) AS Result
where Result.RowCount > 0

GO

Any help appreciated!

What does "doesn't work" mean? Did you try `[RowCount]` which delimits an entity name as opposed to `'RowCount'` which makes it a string? — Aaron Bertrand, Jan 21 '22 at 14:51
`ROWCOUNT` is a [reserved keyword](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/reserved-keywords-transact-sql?view=sql-server-ver15). You delimit identified it in the `SELECT`, but not in the `WHERE`. You need to delimit identify it in both places or, better yet, don't use reserved keywords for object names/aliases. — Thom A, Jan 21 '22 at 14:53
Also, don't use single quotes (`'`) for aliases. Single quotes are for literal strings, not delimit identifying object names. Some methods of using single quotes for Aliases are deprecated and only works when you define them, no where else; `ORDER BY 'value'` would *not* order by your column aliased as `'value'`, it would order by the `varchar` literal `'value'` (so would effectively not order at all). Stick to object and alias names that don't need delimit identifying, and if you *must* delimit identify them use the T-SQL identifier, brackets (`[]`), or ANSI-SQL's, double quotes (`"`). — Thom A, Jan 21 '22 at 15:11
Did you read the comments about the solution you chose? You missed an important discussion of a serious flaw - use the code provided by Rikin Patel. If you only care about tables with rows, maybe accurate row counts are not actually needed? — SMor, Jan 22 '22 at 00:59

score 0 · Answer 1 · answered Jan 21 '22 at 14:52

There are three problems.

SELECT * FROM
(
  SELECT ObjectName = SCHEMA_NAME(obj.[schema_id]) 
                      + '.' + obj.name, 
         [RowCount] = SUM(p.rows) 
  FROM        sys.objects AS obj
  INNER JOIN sys.partitions AS p 
    ON obj.[object_id] = p.[object_id] 
  WHERE       obj.type = 'U' 
  GROUP BY    obj.[schema_id], obj.name
) AS Result
WHERE Result.[RowCount] > 0;

When you move a query into a derived table, subquery, or CTE, all of the columns need to have names.
'Alias' should be [Alias] since the former makes it look like a string and that form is deprecated in some contexts.
As Larnu pointed out, ROWCOUNT needs to be escaped in all spots, not just one.

Can't assign an alias to result of a join - SQL

1 Answers1