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

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

  • Comments 2
  • Likes

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
Comments
  • Hmmm... That's interesting. Where did you first hear about this? Do you have other blog posts I can take a look at?<br><br>Monica Cerna Rodriguez<br>MK Partners Inc<br><a href="http://www.mkpartners.com" title="salesforce consulting">Salesforce Consulting</a> Experts

  • Issues with Syteline infor erp sl8.03

    - Grid Column Views and edits are difficult and inflexible.

    - Ridiculous answer I got from Syteline Support about fixing right

    click menu options so they include copy & paste: "Adding right click

    functionality to Syteline is most often more work than the benefit it

    provides."

    - Ridiculous answer I got from Syteline Support about adding a button to the customer order form:

    Please determine if there is a strong business need for this modification request.

    - Ridiculous answer I got from Syteline Support about On the

    Customer Order Form Grid Window(left pane), to add a new column field

    option to the 'Edit Grid Column' for 'Item Name' (Part#):

    Please determine if there is a strong business need for this modification reque

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment