MS SQL Server – Loop through database tables and columns

Test Environment: MS Server 2008

Stored procedure to loop through all tables and all columns in a database

Change [DATABASE_NAME],[SCHEMA_NAME],[USER_NAME] accordingly

USE [DATABASE_NAME]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROC [SCHEMA_NAME].[loop_Through_Tables_Columns]
AS
BEGIN

	SET NOCOUNT ON
	DECLARE @TableName nvarchar(256)
	DECLARE @ColumnName nvarchar(128)
	SET  @TableName = ''

	WHILE @TableName IS NOT NULL
	BEGIN
		SET @ColumnName = ''
		SET @TableName = 
			(SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES
				WHERE TABLE_TYPE = 'BASE TABLE' /**You can remove this condition to go thourgh with both views and tables**/
					AND	 QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
					AND	OBJECTPROPERTY(
						OBJECT_ID(
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
							 ), 'IsMSShipped'
						       ) = 0
			)
			
		PRINT 'TableName:  '+ CAST(@TableName AS CHAR)
			
			WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
			BEGIN
				SET @ColumnName =
				(
					SELECT MIN(QUOTENAME(COLUMN_NAME))
					FROM INFORMATION_SCHEMA.COLUMNS
					WHERE TABLE_SCHEMA	= PARSENAME(@TableName, 2)
						AND	TABLE_NAME	= PARSENAME(@TableName, 1)
						/**You you want to go thourgh with certain type of data types**/
						AND	DATA_TYPE IN('char','varchar','nchar','nvarchar','text','ntext','image','xml','varbinary')
						AND	QUOTENAME(COLUMN_NAME) > @ColumnName
				)
				PRINT 'ColumnName:  '+ CAST(@ColumnName AS CHAR)				
			END
	END

END
GRANT EXECUTE ON [SCHEMA_NAME].[loop_Through_Tables_Columns] to USER_NAME 

Execute Stored Procedure:

EXEC [SCHEMA_NAME].[loop_Through_Tables_Columns]