Written by Richard on September 9, 2010

Searching all the tables that reference a particular data field (column name)

Here's one for your bag of tricks ... and we can all thank Dave Giusto (once again).

Use the following script (for SQL Server..sorry) when you what to find out all the places where a piece a data may be stored in the visual tables. 

To use the script:

  1. Download the SQL server script Tables & Views Search Script
  2. Open SQL Server Management Studio
  3. Select the visual database and click on the New Query
  4. Paste the script in the query window
  5. Modify the script to search for the data(column) you want. To do this, set the @col_name variable to the name of the column. You can use the '%' as a wildcard..

    For example, to search for CARTON:
    set @col_name ='%carton%'
  6. Next, indicate whether you want the script to search TABLES OR search VIEWS (yes... views too!). To do this, set the @structure variable to either 'U' for TABLES or 'V' for VIEWS.

    For example, to search in tables:
    set @structure = 'U'
  7. Execute the script.

Now, the resulting list may not be 100% complete due to varying naming convention of the column name but the script does provide you with a good starting point

Screen shot of the script


Screen shot of the results:

Dave currently works out of the New York office of Synergy Resources and can be reached there or via the Visual Manufacturing International User Group (VMIUG) under the handle dave_giusto.



TrackBack URL for this entry:

Listed below are links to weblogs that reference Searching all the tables that reference a particular data field (column name) :


Feed You can follow this conversation by subscribing to the comment feed for this post.

The comments to this entry are closed.