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 raise a error
Incorrect syntax near ‘|’.

Please, help me, thank you so much!

>Solution :

What you want is:

select 'select count(*) FROM ' + 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 = @sql + 'select count(*) AS  ''' + QUOTENAME('count_' + name) + ''' FROM ' + QUOTENAME(name) + ';'
from sys.tables
order by name
exec(@sql)