I’m currently working on a Data Conversion and I’ve been trying to discover where certain fields are used within the database. I have a schema, but it doesn’t contain all of the foreign keys as you would hope. So, I can find the values of the field but I need to know which other tables that this might be used in. Example if you have a Control File that is the home for multiple picklist lookups, it should be in there.
Well, I search the Internet for a solution to this and Matt had an answer. Warning, this script will take a long time to run… Duh! it is searching every text field in the entire database for the value.
I did modify the code Matt wrote to tell me where it was at at it was going along…. On my VPC with the amount of data I’m looking at, it could be hours and I would like to know where it is at and when it finds something.
The first step is to declare some variables that will be used. These variables will store the value we are searching for, the dynamic SQL query, and the table and column names which we will use to build the query. There will also be a temporary table created to store the results of the query.
Next we will declare the cursor and load with with the data we will be using. The select statement used will be pulling the table and column names by joining together the sysobjects and syscolumns tables.
There are a few things to note. We are only looking at records in the sysobjects table with the type field set to ‘U’ which represents user tables. Also, in this example we are searching for a string, so we’re only looking at records in syscolumns that are CHAR, NCHAR, VARCHAR, and NVARCHAR. You can find the numeric values for the different data types in systypes.
Now we’ll use the data stored in the cursor to build and run the dynamic queries.
In this example, I just select the results of the dynamic query, but you could do many other more useful things.
Oh, and don’t forget to delete that temporary table when you’re done.
DROP TABLE #t