question

schoolboy-2290 avatar image
0 Votes"
schoolboy-2290 asked YitzhakKhabinsky-0887 answered

Finding or listing fields in tables?

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-general
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

cooldadtx avatar image
0 Votes"
cooldadtx answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

NaomiNNN avatar image
0 Votes"
NaomiNNN answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yufeishao-msft avatar image
0 Votes"
Yufeishao-msft answered

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.






5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered

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.

201496-redgate-sql-search.jpg



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.