Page 179 - SQL

Chapter 48: SQL CURSOR

Examples

Example of a cursor that queries all rows by index for each database

Here, a cursor is used to loop through all databases.
Futhermore, a cursor from dynamic sql is used to query each database returned by the first cursor.

This is to demonstrate the connection-scope of a cursor.

DECLARE @db_name nvarchar(255)
DECLARE @sql nvarchar(MAX)

DECLARE @schema nvarchar(255)
DECLARE @table nvarchar(255)
DECLARE @column nvarchar(255)

DECLARE db_cursor CURSOR FOR
SELECT name FROM sys.databases

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @db_name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'SELECT * FROM ' + QUOTENAME(@db_name) + '.information_schema.columns'
PRINT ''
PRINT ''
PRINT ''
PRINT @sql
-- EXECUTE(@sql)

-- For each database

DECLARE @sqlstatement nvarchar(4000)
--move declare cursor into sql to be executed
SET @sqlstatement = 'DECLARE columns_cursor CURSOR FOR SELECT TABLE_SCHEMA, TABLE_NAME,
COLUMN_NAME FROM ' + QUOTENAME(@db_name) + '.information_schema.columns ORDER BY TABLE_SCHEMA,
TABLE_NAME, ORDINAL_POSITION'

EXEC sp_executesql @sqlstatement

OPEN columns_cursor
FETCH NEXT FROM columns_cursor
INTO @schema, @table, @column

https://riptutorial.com/ 161