HEELPBOOK - SQL Server - List Count of Rows in All Tables in Database using a 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.

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


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

############ ARTICLE INFO #############

Article Month: March
Article Date: 12/03/2012
Permalink: http://heelpbook.altervista.org/2012/sql-server-list-count-of-rows-in-all-tables-in-database-using-a-cursor/
Source: http://www.kodyaz.com/articles/sql-cursor-example-tables-rows-count-using-sql-server-cursor.aspx
Language: English

View more articles on: http://www.heelpbook.net/
Follow us on Facebook: http://it-it.facebook.com/pages/HeelpBook/100790870008832
Follow us on Twitter: https://twitter.com/#!/HeelpBook
Follow us on RSS Feed: http://feeds.feedburner.com/Heelpbook

Images: 

- http://heelpbook.altervista.org/wp-content/uploads/2012/02/SQL-Server---List-Count-of-Rows-in-All-T_AF5F/sql-cursor-example-sql-server-cursor-output.png