How-To: Create Cursor in TSQL

I am sure you’ve been hearing or reading debates on why not to use CURSOR in SQL Server. I’m glad that is beyond the scope of this yet another How-to article here in my blog.

Although some people would say not to use cursor, I have noticed that lots of people are still asking for the basics of cursor. Well, this article is for those folks who want to know how to create a cursor in SQL Server using TSQL.

In order for you to successfully run this sample, you need the AdventureWorks database.

DECLARE @tablename VARCHAR(60)

DECLARE cursor_tablenames CURSOR FOR
SELECT name FROM AdventureWorks.sys.tables

OPEN cursor_tablenames
FETCH NEXT FROM cursor_tablenames INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT  t.name AS [TABLE Name], c.name AS [COLUMN Name], p.name AS [DATA Type], p.max_length AS[SIZE],   CAST(p.PRECISION AS VARCHAR) +'/'+ CAST(p.scale AS VARCHAR) AS [PRECISION/Scale]
FROM AdventureWorks.sys.objects AS t
JOIN AdventureWorks.sys.columns AS c
ON t.OBJECT_ID=c.OBJECT_ID
JOIN AdventureWorks.sys.types AS p
ON c.system_type_id=p.system_type_id
WHERE t.name = @tablename
AND t.type_desc='USER_TABLE'
ORDER BY t.name ASC

FETCH NEXT FROM cursor_tablenames INTO @tablename
END

CLOSE cursor_tablenames
DEALLOCATE cursor_tablenames

This will return the User Tables and their attributes in a single result set [See Comment by Josef – Marlon Ribunal]: