There will come a time in every developer’s life where we will need to know a little SQL, or to have enough knowledge to find things within an SQL database. When that time arrives, I hope the following few SQL Server tips will help you like they have helped me.
SQL Server Tip 1: Find All Column References That Are Not Keys
There are many times when you need to find all the tables that contain a specific column name. If a database is created correctly, or normalized with foreign keys declared, this is an easy task in “Sql Server Management Studio”. All you need to do is right-click on the table with the column in question. In Figure 1 you will see the context menu that will appear.
From the context menu select “View Dependencies” and the “Object Dependencies” window will display, which you can see in Figure 2.
In Figure 2 you can see there are two options: window objects that depend on the currently selected table, and objects that this table depends on. Figure 2 shows the tables that “ClientData” depend on, and one of those tables is “XmlType”. This doesn’t tell you exactly what column it depends on, but if you look at both tables columns you will see a column named “XmlTypeId” which is often how you find the answer.
The flaw with “View Dependencies” is that most databases are not created correctly for many reasons. One reason is that you are working with a data warehouse where foreign keys were not added to speed up inserts. Maybe a developer created the tables and decided to enforce integrity inside the code. Or maybe it was a mistake. All the reasons happen and probably many more but that shouldn’t stop you from finding all the tables that contain the column you are looking for. An example of an application where you will find this often is “Dynamics GP”. In Figure 3 I show you the “View Dependencies” screen from the “Payroll Master” table (UPR00100). Here I am showing objects that depend on this table, of which there are two. But the two objects that depend on the table are views and not tables. I would expect to see a reference to the “Payroll Address Master” (UPR00102) which does have a reference by the column “EMPLOYID” but it isn’t shown.
When you run into these scenarios where there is no table dependencies declared, there are still ways to find the references you are looking for. I am going to continue to use my “Dynamics GP” example to show all the actual references to the “Payroll Master” table. Since I know that the primary key of this table is “EMPLOYID”, I will create an SQL statement to find all the assumed references to the “Payroll Master” table. In Figure 4 you can see this SQL statement along with a portion of the result set from the query. When you inspect the query you will see that I am referencing the view “INFORMATION_SCHEMA.COLUMNS”. This view is one of many views in the “INFORMATION_SCHEMA” schema that the SQL server exposes to the user. These views allow you to find metadata about SQL server objects easily, without have to know a lot about SQL server. There are other ways to find this exact same information using system tables but I have found that the “INFORMATION_SCHEMA” views are easier to remember and have been very valuable to me in learning SQL server and helping me with developing applications. To learn more about this schema Microsoft has published a list of all the views here.
It’s helpful to have these tables that contain references to my column, but I need to determine what exactly is in these tables. Now you can use SQL to write SQL and find out. In Figure 5 I write a SQL statement that will create a select for each of the tables returned in Figure 4 with the table name as the first column and all the rest of the columns following. I use “Top 5” to only get a small sample of the data from each table, but that can be changed depending on your personal needs.
I hope this can help you find information about databases you are working with or help with the development of your next application when you need to know all the references for maybe a cascading delete or just because you are curious like me.
In my next blog I will write how you can use the “INFORMATION_SCHEMA.ROUTINES” view in a very similar manner.