question

You can use the report designer to create a query. From there you can "Add" a table which then should pull up the list of tables. However all this requires that you have access to the schema. If you don't then none of this works and there is not much you can do about it.

But if you have schema access then start up SSMS (you said proprietary database so this may not work). Then you can view the tables and search from there. If you cannot use SSMS then there isn't much you can do.

I would go back to the DB provider and ask them either to provide a data dictionary and/or provide the relevant queries to you. I would find it odd that you've been asked to write a report but not given either access to the DB to figure it out or the data dictionary so you can write it. That's like asking you to build an app but not telling you what the app should do.

In SSMS you can use INFORMATION_SCHEMA.Columns view to find tables with column having such a name by using a pattern match.

Hi @schoolboy-2290，

You can refer to this thread:
Find a value anywhere in a database

Like:

 CREATE PROC SearchAllTables
 (
     @SearchStr nvarchar(100)
 )
 AS
 BEGIN
    
    
 DECLARE @Results TABLE(ColumnName nvarchar(370), ColumnValue nvarchar(3630))
    
 SET NOCOUNT ON
    
 DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
 SET  @TableName = ''
 SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')
    
 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'
             AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
             AND OBJECTPROPERTY(
                     OBJECT_ID(
                         QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                          ), 'IsMSShipped'
                            ) = 0
     )
    
     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)
                 AND DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar')
                 AND QUOTENAME(COLUMN_NAME) > @ColumnName
         )
    
         IF @ColumnName IS NOT NULL
         BEGIN
             INSERT INTO @Results
             EXEC
             (
                 'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630) 
                 FROM ' + @TableName + 
                 ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
             )
         END
     END 
 END
    
 SELECT ColumnName, ColumnValue FROM @Results
 END

It searches all columns of all tables in a given database

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Hi @schoolboy-2290,

You can try Red-Gate SQL Search utility for your needs.
It is a SSMS add-in, and it is free of charge.

Here is its download link: SQL Search

Please see below how it looks like in the SSMS.