Finding or listing fields in tables?

schoolboy 1 Reputation point
2022-05-11T13:41:02.31+00:00

Hoping this is the right location for this.
I'm using ReportBuilder to create a report. I'm working with a proprietary database and the company is not interested in sharing any details about the database with us.
Database literally has hundreds of tables, each specific to a specific module in the application, many with the same field name, but not the same data in them. For example, I'm trying to pull some HR employee data, but want to filter out terminated employees. The data I want is in multiple tables and I've got the data I want but I'm getting every employee in the DB. I've tried some fields in both the HR module and the payroll module but am still getting everyone in the DB.

Is there any way to do a "find" when I'm in report design that I can do a search on the various tables to find fields called "termination" or something similar?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
14,150 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Michael Taylor 55,471 Reputation points
    2022-05-11T14:10:51.087+00:00

    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.

    0 comments No comments

  2. Naomi Nosonovsky 7,971 Reputation points
    2022-05-11T14:12:07.5+00:00

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

    0 comments No comments

  3. YufeiShao-msft 7,131 Reputation points
    2022-05-12T07:05:37.683+00:00

    Hi @schoolboy

    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.

    0 comments No comments

  4. Yitzhak Khabinsky 26,201 Reputation points
    2022-05-12T13:08:01.223+00:00

    Hi @schoolboy ,

    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.

    201496-redgate-sql-search.jpg

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.