问题

I'm using SQL Server, I want to count all the rows in all tables in the database

The following statement I have read in the book (O-Reilly-SQL-Cookbook-2nd-Edition-Final)

select 'select count(*) from '||table_name||';' cnts
from user_tables;

When I run on Microsoft SQL Server Management Studio, it raises an error
Incorrect syntax near '|'.

I know it only runs on Oracle, So I need a version of SQL Server. Because I'm new about dynamic SQL.

Please, help me, thank you so much!

select 'select count(*) from '||table_name||';' cnts
from user_tables;

答案1

得分: 2

你正在使用SQL Server，它不支持管道运算符。请使用 + 替代。

答案2

得分: 1

Here is the translated SQL code:

你需要的是：

```sql

select 'select count(*) FROM ' + QUOTENAME(name)
from sys.tables

如果你想直接执行生成的查询，可以使用类似以下的方式：

declare @sql nvarchar(max);
select @sql = string_agg(
  'select count(*) AS  ' + QUOTENAME('count_' + name) + ' FROM ' + QUOTENAME(name) + ';', '
') within group (order by name)
from sys.tables;
exec sp_executesql @sql;

请注意，我只提供了代码的翻译部分，没有包含其他内容。

<details>
<summary>英文:</summary>

What you want is:

```sql

select &#39;select count(*) FROM &#39; + QUOTENAME(name)
from sys.tables

if you want to directly execute the generated queries, you can use something like:

declare @sql nvarchar(max);
select @sql = string_agg(
  'select count(*) AS  ' + QUOTENAME('count_' + name) + ' FROM ' + QUOTENAME(name) + ';', '
') within group (order by name)
from sys.tables;
exec sp_executesql @sql;