Finding or listing fields in tables?

Question

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?

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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.