Accessing data at the table level using SQL Management Studio
Once we have identified our physical table name, we can begin to use SQL Management Studio to view the data in the table. To accomplish this, we can write simple select statements to view the data. This can be very useful, as it allows us to see actual data in the underlying table. So, if a user tells us they need information they are entering in Sales User defined fields, we can view that data directly in the table to be sure we are capturing the requested data. A helpful tip for this is to open the Sales User-Defined Fields Entry window in Dynamics GP for a specific document number. Then, in SQL Management Studio, we can select the record from the table (in this case, the SOP10106 table) that equals the same document number we are looking at in the screen. This allows us to ensure we are looking at the correct data.
Note
One thing to keep in mind if we are working on a production database or even a test database with large amounts of data is that we can use commands such as NOLOCK, TOP 1, TOP 10, etc. to keep us from putting inadvertent locks on the table and to limit the amount of data returned by our queries. We don't necessarily need to select all records from a Sales Transaction table to see the data in that table and begin writing our report. It is much more efficient to use small selections of data at this point in our data gathering.
A sample of these TSQL statements can be seen in the following image:
In SQL Management Studio, there is another very useful command that we have at our disposal. This command is called SP help and is very easy to use. We simply open a new query window and type in sp_help and our table name (sp_help SOP10100). This will return information such as the owner of the table and the created data, along with all of the columns in the table, their names, types, whether they are computed, their length, and if they are nullable amongst other things. We will also be provided with the indexes on the table. All of this information can be helpful to us when we begin writing our reports.