SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP

SQL Cursor Example - List Count of Rows in All Tables in Database using SQL Server Cursor


In this document, sql developers will find a SQL cursor example t-sql code to list number of rows (record counts) in all user tables in a MS SQL Server database.

Note that with the T-SQL enhancements introduced with MS SQL Server 2005 and MS SQL Server 2008, developers and database administrators can find ways to avoid using SQL Server cursor in their sql codes in their jobs.

Do not use frequently sql cursor in production system during high load times. SQL cursors if not done in the correct declaration can effect the performance of database applications in negative manner.
So if possible prevent developers use transact-sql cursor in their sql scripts.

If you want more on SQL Server Cursor and find more sql cursor sample, you can go to sql tutorial How to Create and Use a Sample SQL Cursor and T-SQL Cursor Code.





In the following SQL Server cursor, you will first notice the cursor declaration in sql script.
You can declare sql cursor using DECLARE cursorname CURSOR syntax.

This sample sql cursor is build over a list of user tables defined in a database, and is used for listing the count of rows in each database table.
Since for each table a sql select query is build and executed seperately, the sql cursor or the loop for running select task for each table consumes considerable resource.
Do not forget, sql engine is built and optimized for batch processes not for single row processes. So if possible avoid from using sql cursor in sql codes.


SQL Cursor Example

Here is a sql cursor example :

DECLARE @TableName sysname
DECLARE @SQL nvarchar(max)

DECLARE tables_cursor CURSOR FAST_FORWARD
FOR
SELECT name FROM sys.tables

OPEN tables_cursor

FETCH NEXT FROM tables_cursor INTO @TableName

WHILE @@FETCH_STATUS = 0
BEGIN
  SELECT @SQL =
    'SELECT ''' + @TableName + ''', COUNT(*) as RowsCount ' +
    'FROM [' + @TableName + ']'
  EXEC SP_EXECUTESQL @SQL
  FETCH NEXT FROM tables_cursor INTO @TableName
END

CLOSE tables_cursor
DEALLOCATE tables_cursor
Code

And when I execute the above sample sql cursor code on one of my SQL Server databases, I get the following sample t-sql cursor output :

sample t-sql cursor code output

The above cursor is declared as FAST_FORWARD which is faster than other cursor declaration types.


Additional SQL Cursor Tutorials and T-SQL Cursor Examples

You can find more sql cursor tutorials and sql cursor example at the following articles :
How to Create and Use a Sample SQL Cursor and T-SQL Cursor Code
T-SQL Cursor Example Code

You can find more on cursor types in sql on MSDN.
I hope you find this sql cursor example useful.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.