Loop through records without using a cursor

,

I sometimes have to loop through records in a database and perform a specific action on the value that is returned.

For example, In the script below I loop through the user tables in sysobjects and simply print them out.

This technique is useful when dropping all indices/triggers on a particular table, or adding WITH ENCRYPTION to stored procedures on a live server.

Another use is on development servers where the table structure changes and you want to recompile all stored procedures and refresh all views.

DECLARE @objName VARCHAR(50)
SET	@objName = ''

WHILE	@objName IS NOT NULL
	BEGIN
		SELECT @objName = MIN( Name )
		FROM	SysObjects
		WHERE	Type='U' AND
			Name > @objName

		IF	@objName IS NOT NULL
			BEGIN
				--	Insert code to do stuff here.
				PRINT  @objName 
			END
	END


* * *
Example to recompile all stored procs and refresh all views.

DECLARE @objName VARCHAR(50)
SET	@objName = ''

WHILE	@objName IS NOT NULL
	BEGIN
		SELECT @objName = MIN( Name )
		FROM	SysObjects
		WHERE	Type='V' AND
			Name > @objName

		IF	@objName IS NOT NULL
				exec sp_refreshview @objName		END
SET	@objName = ''

WHILE	@objName IS NOT NULL
	BEGIN
		SELECT @objName = MIN( Name )
		FROM	SysObjects
		WHERE	Type='U' AND
			Name > @objName

		IF	@objName IS NOT NULL
				exec sp_recompile @objName		END

Rate

3 (2)

Share

Share

Rate

3 (2)