为什么我不能在SQL视图中查询单个列？

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.

我使用了一个脚本来创建一个视图,该视图具有数据库中每个表的表名,以及相应表中的数据行数。 SELECT *一切正常。但是,我只想查询某些行,但我似乎无法这样做。

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

视图是使用以下脚本创建的(对于脚本,可归功于DatabaseZone.com):

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:

当我对结果视图运行Select *时,我得到的结果如下:

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:

如果我只查询tableName列,它工作正常并返回表名称。但是,如果我也尝试查询RowCount列,我会在关键字'RowCount附近收到错误消息错误语法。无论我是否限定数据库,都会发生这种情况 - 它似乎无法将RowCount识别为我可以在查询中调用的有效列。所以这个脚本失败了:

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.

这是怎么回事?我希望能够在我的查询中对视图中的列名称进行别名,但只要我不能引用RowCount列,我就不能这样做。

(FYI running this in SQL Server 2014)

(仅供参考,在SQL Server 2014中运行此功能)

2 个解决方案

#1

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

Rowcount是保留字,您可以使用[]选择保留字:

[Rowcount]

#2

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.

感谢@sgeddes指出方向。删除了视图,更改了创建它的脚本以使用行计数列的其他名称,现在它按预期工作。问题是Rowcount是一个保留字的冲突。

Changed the create table script on this line:

更改了此行上的create table脚本:

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.

...此时我现在可以根据需要引用Row_Count列。

#1