CODEWITHSUNDEEP
sqlsql-serverview
Scro
Scro

Reputation: 1443

Why can't I query individual columns in a SQL view?

I've used a script to create a view that has the table name for every table in a database, along with the number of rows of data in the respective table. Everything works fine with SELECT *. However, I'd like to query only certain rows, but I can't seem to do that.

The view was created with the following script (credit to DatabaseZone.com for the script):

CREATE VIEW RowCount_AllTables 
AS
    SELECT DISTINCT
        sys.schemas.name, sys.tables.name AS tableName, 
        sys.dm_db_partition_stats.row_count AS 'RowCount'
    FROM 
        sys.tables 
    INNER JOIN
        sys.dm_db_partition_stats ON sys.tables.object_id = sys.dm_db_partition_stats.object_id 
    INNER JOIN
        sys.schemas ON sys.tables.schema_id = sys.schemas.schema_id
    WHERE     
        (sys.tables.is_ms_shipped = 0)

When I run Select * against the resulting view, I get results such as:

name    tableName     RowCount
dbo     Table1          2
dbo     Table2          1230
dbo     Table3          0

If I query just the tableName column, that works fine and returns the table names. However, if I try to query the RowCount column as well, I get the error message Incorrect syntax near the keyword 'RowCount. This happens regardless of whether I qualify the database -- it seems to not recognize RowCount as a valid column that I can call in a query. So this script fails:

SELECT RowCount 
FROM RowCount_AllTables;

But this one works:

SELECT tableName 
FROM RowCount_AllTables;

What's going on? I'd like to be able to alias the column names in the view in my query but I can't do that so long as I can't reference the RowCount column.

(FYI running this in SQL Server 2014)

Upvotes: 1

Views: 84

Answers (2)

Scro
Scro

Reputation: 1443

Thanks to @sgeddes for pointing the way. Dropped the view, changed the script for creating it to use another name for the row count column, and it now works as expected. The issue was a conflict with Rowcount being a reserved word.

Changed the create table script on this line:

SELECT  distinct  sys.schemas.name, sys.tables.name AS tableName,
 sys.dm_db_partition_stats.row_count AS 'RowCount'

to:

SELECT  distinct  sys.schemas.name, sys.tables.name AS tableName,
 sys.dm_db_partition_stats.row_count AS 'Row_Count'

...at which point I can now reference the Row_Count column as desired.

Upvotes: 0

Aaron Dietz
Aaron Dietz

Reputation: 10277

Rowcount is a reserved word, you can select reserved words using [] as:

[Rowcount]

Upvotes: 1

Related Questions