INFOR ERP: Finding all tables that uses a specific column name

  • Article

Author:

This article is written by our contributing author Ken Lassesen. His bio can be found here

. With INFOR Enterprise Resource Planning (ERP), administrators often need to know where a specific column occurs in the many tables of the database. This issue is not unique to INFOR and applies to many ISV products.

 

I noticed that this problem has been cited by Richard Ferlatte, an INFOR blogger with cut and paste code being provided.

I have improved this code in the TSQL Script below to create a stored procedure with several enhancements not in the above post:

  • Include Schema Name
  • Include User Data Types (UDT)
  • Providing the number of rows in each table
  • I retain support for wild carding column names (Party%)

I also added an two stored procedures that may be helpful when trying to isolate data problems.

  • [p_FindColumnsWithCount] provides the number of rows in each table without column names showing; this is helpful when trying to isolate problems.
  • [p_FindColumnsWithCountDetail] provides the number of rows in each table with column names showing; this is helpful when trying to isolate problems.

Listing Columns and Tables

Example of use and results:

  • p_FindColumns @ColumnName='Party%'

 

  • p_FindColumns @ColumnName='Party%',@Type='View'

 

To create this utility put this into SSMS and execute.

 CREATE proc p_FindColumns
    @ColumnName sysname='%',
    @Type varchar(5)=NULL -- Choices 'Table','view'
AS
IF @Type IS NULL
SELECT Column_Name,'['+TABLE_SCHEMA+'].['+TABLE_NAME+']' as TableName,  
       CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NULL 
        THEN DATA_TYPE
        ELSE
           DATA_TYPE+'('
           +Cast(CHARACTER_MAXIMUM_LENGTH as varchar(11))+')'
        END,
        type_Desc,
        DOMAIN_NAME          
FROM INFORMATION_SCHEMA.COLUMNS    
JOIN SYS.Objects T
ON T.Name=Table_Name
AND SCHEMA_NAME(schema_id)=TABLE_SCHEMA   
WHERE Column_Name LIKE @ColumnName 
ELSE
SELECT Column_Name,'['+TABLE_SCHEMA+'].['+TABLE_NAME+']' as TableName,  
       CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NULL 
        THEN DATA_TYPE
        ELSE
           DATA_TYPE+'('
           +Cast(CHARACTER_MAXIMUM_LENGTH as varchar(11))+')'
        END,
        type_Desc,
        DOMAIN_NAME          
FROM INFORMATION_SCHEMA.COLUMNS    
JOIN SYS.Objects T
ON T.Name=Table_Name
AND SCHEMA_NAME(schema_id)=TABLE_SCHEMA   
WHERE CHARINDEX(type_Desc,@Type) > 0
AND Column_Name LIKE @ColumnName 
go

Listing Tables and Row Counts

Example of use and results:

  • p_FindColumnsWithCount @ColumnName='Party%'

 

To create this utility put this into SSMS and execute.

 CREATE proc p_FindColumnsWithCount
    @ColumnName sysname='%'
AS 
SET NOCOUNT ON
DECLARE @CMD nvarchar(max)
DECLARE @Schema nvarchar(max)
DECLARE @Table nvarchar(max)
CREATE TABLE #TblCnt(SName sysname,TName sysname,CName sysname)
INSERT INTO #tblCnt(SName,TName,CName)
SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME          
FROM INFORMATION_SCHEMA.COLUMNS    
JOIN SYS.Objects T
ON T.Name=Table_Name
AND SCHEMA_NAME(schema_id)=TABLE_SCHEMA   
WHERE Column_Name LIKE @ColumnName 
AND TYPE='U'
SET @CMD=''
DECLARE TR_Cursor CURSOR FOR
SELECT DISTINCT
    SName,
    TName
FROM #tblCnt
OPEN TR_Cursor;
FETCH NEXT FROM TR_Cursor INTO @Schema,@Table
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CMD=@CMD +'SELECT ''['+@Schema+'].['+@Table
    +']'', Count(1) AS ROWS FROM ['
    +@schema+'].['+@Table+'] '
    
    FETCH NEXT FROM TR_Cursor INTO @Schema,@Table
    IF @@FETCH_STATUS = 0
        SET @CMD=@CMD+ CHAR(10)+' UNION '+ CHAR(10)
END;
CLOSE TR_Cursor;
DEALLOCATE TR_Cursor;
EXEC (@CMD)
Go

Listing Table for Counts for Columns

NOTE: Before this one will work, you must create [p_FindColumnsWithCount] shown above.

Example of use and results:

  • p_FindColumnsWithCountDetail @ColumnName='Party%'

 

 CREATE proc p_FindColumnsWithCount
    @ColumnName sysname='%'
AS 
SET NOCOUNT ON
DECLARE @CMD nvarchar(max)
DECLARE @Schema nvarchar(max)
DECLARE @Table nvarchar(max)
CREATE TABLE #TblCnt(SName sysname,TName sysname,CName sysname)
INSERT INTO #tblCnt(SName,TName,CName)
SELECT TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME          
FROM INFORMATION_SCHEMA.COLUMNS    
JOIN SYS.Objects T
ON T.Name=Table_Name
AND SCHEMA_NAME(schema_id)=TABLE_SCHEMA   
WHERE Column_Name LIKE @ColumnName 
AND TYPE='U'

SET @CMD=''
DECLARE TR_Cursor CURSOR FOR
SELECT DISTINCT
    SName,
    TName
FROM #tblCnt
OPEN TR_Cursor;
FETCH NEXT FROM TR_Cursor INTO @Schema,@Table
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @CMD=@CMD +'SELECT ''['+@Schema+'].['+@Table
    +']'' AS [TableName], Count(1) AS ROWS FROM ['
    +@schema+'].['+@Table+'] '
    
    FETCH NEXT FROM TR_Cursor INTO @Schema,@Table
    IF @@FETCH_STATUS = 0
        SET @CMD=@CMD+ CHAR(10)+' UNION '+ CHAR(10)
END;
CLOSE TR_Cursor;
DEALLOCATE TR_Cursor;
EXEC (@CMD)
go