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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 |
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:
|
1 |
EXEC [SCHEMA_NAME].[loop_Through_Tables_Columns] |