Cursors and DynamicSQL

---
--- Explore using Cursors, and dynamic SQL
---
--- As an example, we will find all columns in the database that contain a string with a trailing space
---

use ReportBuilderTraining


--- make sure there is some data with a trailing space

update Student set FirstName = 'Ed ' where StudentId=1
go

select '|' + FirstName + '|', * 
	from Student 
	where FirstName='Ed'
go

---
--- Iterate through a cursor for each table in the database, generate a SQL statement for each column, and execute it dynamically
---

DECLARE @cmd nvarchar(4000)

DECLARE MyCursor CURSOR for

	select 'if exists (select * from ' + c.TABLE_SCHEMA + '.' + c.TABLE_NAME + ' where datalength(' + c.COLUMN_NAME + ')!=len(' + c.COLUMN_NAME + ')) print ''' + c.TABLE_SCHEMA + '.' + c.TABLE_NAME + '.' + c.COLUMN_NAME + ''''
		from information_schema.columns c
		join INFORMATION_SCHEMA.tables t
		on c.table_name=t.table_name and c.TABLE_SCHEMA=t.table_schema
		where data_type='varchar'
		and t.TABLE_TYPE='BASE TABLE'
		order by c.TABLE_SCHEMA, c.TABLE_NAME

OPEN MyCursor

FETCH NEXT FROM MyCursor INTO @cmd

WHILE @@FETCH_STATUS = 0
BEGIN

	print @cmd

	execute sp_executesql @cmd

	FETCH NEXT FROM MyCursor INTO @cmd

END

CLOSE MyCursor
DEALLOCATE MyCursor