Written by Rich 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

Sqlscript


Screen shot of the results:
Sqlscriptresults


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

TrackBack URL for this entry:
http://www.typepad.com/services/trackback/6a01053702bb53970c0134872ba0b6970c

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

Comments

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

The comments to this entry are closed.

WHO IS VISUALNUGGETS?

Rich Ferlatte
Results Driven VISUAL Specialist

CONSULTING SERVICES

Of course, I am available for hire!

ERP Implementations & Upgrades
Analytics & Reporting
Integrations & Macros
General VISUAL Support

ONLINE VISUAL WORKSHOPS

Taking it to the next level and designing a program around "Hands On VISUAL Training".

Check them out - there's sure to be one to interest you!

Operational Workshops
Reporting Workshops
Financial Workshops
Inventory Workshops
Quality Workshops

VISUAL EXTENSIONS

BOM Xploder
Drastically improve your BOM visibility and discover shortages at ANY level of your bill of material no matter how deep.
SmartViews
See, analyze, and track information that helps you better support individual, departmental, and your company’s performance metrics and goals.
Credit Card Integration
Easily accept and process credit card payments right from within VISUAL.
CAD Integration with CADLink
Reduce rework, minimize scrap, and work more efficiently with CADLink - a fully automated, two-way connection between VISUAL and your CAD system.
Sales Tax Automation with AvaTax
Easily automate sales and use tax in VISUAL with AvaTax. No more manually entering data or uploading rate tables and tax information.
Workforce Management with Kebrite Solutions
Streamlines the collection of Job, Labor, Payroll, Time & Attendance, and a vast array of HR related data-- with live VISUAL integration.
ACH Document Processing
Leveraging VISUAL's payment scheduling and batch payment functions, with the added convenience, performance, and security of Electronic Funds Transfer payment processing.