Dushyanth Jyothi

T-SQL code [Stored Procedure] to loop through all tables and all columns in a database

Applies to MS SQL SERVER 2008

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


Stored Procedure:

 T-SQL | 
 copy code |
?
 
01
USE [DATABASE_NAME]
02
GO
03
SET ANSI_NULLS ON
04
GO
05
SET QUOTED_IDENTIFIER ON
06
GO
07
CREATE PROC [SCHEMA_NAME].[loop_Through_Tables_Columns]
08
AS
09
BEGIN
10
 
11
	SET NOCOUNT ON
12
	DECLARE @TableName NVARCHAR(256)
13
	DECLARE @ColumnName NVARCHAR(128)
14
	SET  @TableName = ''
15
 
16
	WHILE @TableName IS NOT NULL
17
	BEGIN
18
		SET @ColumnName = ''
19
		SET @TableName = 
20
			(SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)) FROM INFORMATION_SCHEMA.TABLES
21
				WHERE TABLE_TYPE = 'BASE TABLE' /**You can remove this condition to go thourgh with both views and tables**/
22
					AND	 QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
23
					AND	OBJECTPROPERTY(
24
						OBJECT_ID(
25
							QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
26
							 ), 'IsMSShipped'
27
						       ) = 0
28
			)
29
 
30
		PRINT 'TableName:  '+ CAST(@TableName AS CHAR)
31
 
32
			WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)
33
			BEGIN
34
				SET @ColumnName =
35
				(
36
					SELECT MIN(QUOTENAME(COLUMN_NAME))
37
					FROM INFORMATION_SCHEMA.COLUMNS
38
					WHERE TABLE_SCHEMA	= PARSENAME(@TableName, 2)
39
						AND	TABLE_NAME	= PARSENAME(@TableName, 1)
40
						/**You you want to go thourgh with certain type of data types**/
41
						AND	DATA_TYPE IN('char','varchar','nchar','nvarchar','text','ntext','image','xml','varbinary')
42
						AND	QUOTENAME(COLUMN_NAME) > @ColumnName
43
				)
44
				PRINT 'ColumnName:  '+ CAST(@ColumnName AS CHAR)				
45
			END
46
	END
47
 
48
END
49
GRANT EXECUTE ON [SCHEMA_NAME].[loop_Through_Tables_Columns] TO USER_NAME 

Execute Stored Procedure:

 T-SQL | 
 copy code |
?
 
1
EXEC [SCHEMA_NAME].[loop_Through_Tables_Columns]