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)