This query uses a CTE so that we can use the derived column table_name in the GROUP BY:
; WITH CTE AS (
SELECT quotename(s.name) + '.' + quotename(o.name) AS table_name
FROM sys.objects o
JOIN sys.schemas s ON o.schema_id = s.schema_id
JOIN sys.columns c ON o.object_id = c.object_id
)
SELECT table_name, COUNT(*)
FROM CTE
GROUP BY table_name
ORDER BY table_name
Here is a an example on how to write a cursor. Note that cursors is something you use only sparingly.
DECLARE @cur CURSOR,
@name sysname
SET @cur = CURSOR STATIC FOR
SELECT name FROM sys.objects ORDER BY name
OPEN @cur
WHILE 1 = 1
BEGIN
FETCH @cur INTO @name
IF @@fetch_status <> 0
BREAK
PRINT @name
END