How to resolve red invalid icon for Informatica mapping

In addition to the command line \d+ <table_name> you already found, you could also use the Information Schema to look up the column data, using information_schema.columns:

SELECT *
  FROM information_schema.columns
 WHERE table_schema = 'your_schema'
   AND table_name   = 'your_table'
     ;

Note: As per the example above, make sure the values are enclosed within quotes.

From BOL, on INFORMATION_SCHEMA.ROUTINES:

Column name: ROUTINE_DEFINITION
Data type: nvarchar(4000)
Description: Returns the first 4000 characters of the definition text of the function or stored procedure if the function or stored procedure is not encrypted. Otherwise, returns NULL. To ensure you obtain the complete definition, query the OBJECT_DEFINITION function or the definition column in the sys.sql_modules catalog view.

You can therefore view the rest by changing you script as follows:

SELECT 
    ROUTINE_NAME, 
    ROUTINE_TYPE, 
    ROUTINE_DEFINITION as First4000, 
    OBJECT_DEFINITION(object_id(ROUTINE_NAME)) as FullDefinition
FROM 
    INFORMATION_SCHEMA.ROUTINES
WHERE 
    OBJECT_DEFINITION(object_id(ROUTINE_NAME)) LIKE @searchText
ORDER BY 
    ROUTINE_NAME

The column aliased above as FullDefinition will return the rest of the content as nvarchar(max).