Where have all the objects gone, long time passing ? (Version 0.1.0)
(You find the script at the end of the blog post)
People who inherit databases encounter often the same problem. They either don´t have the time “learning” the internals / logic of the existing objects within that database, or the learning curve and the time needed is too high for getting started. So cleaning up databases or checking for internal consistency is avoided in order “not to touch a running system”.
So, simple things get out of focus like
- Is the object used anymore by some internal database business logic ?
- Is the object being referenced by any objects in the database ?
- Does the object have any reference in the database to objects that do not exist anymore ?
- When was the table accessed the last time ? Am I able to delete it as not being referenced for a decent amount of time ? (Will be covered in one of the next blog posts)
To be clear, due to the existing dynamic nature of TSQL, not all questions can be answered exactly. But for the object information being modelled by the engine, the questions we have to ask the engine and the answers can be quite easy. I will concentrate on some of the most asked questions here and provide some scripts for you in order to try them on your databases as well, finding the information easily.
Hello ? Does anyone care about me ?
This is a quite easy thing. Beginning with SQL Server 2008, there are special views showing the interactions between existing database schema elements like tables / views / procedures. Without going into detail, I would encourage you to take a look at the system view sys.dm_sql_referencing_entities (MSDN) as in the following sample:
Yay, easy, right ? I see that the SomeTable table is referenced by the view SomeView and that I should not easily drop it before checking with the creator / owner of the view.
Here come the part with the dynamic TSQL culprit. As you can compose your TSQL statement on the fly and it would be compiled at runtime, the view will not find any references to statements like EXEC(‘SELECT SomeCol FROM dbo.SomeTable’) as string will not be parsed for correctness. So checking with the table is only one step of checking for the references. If you are sure that nor internal logic of the database (like procedures) are doing not dynamic stuff, as well as not external APIs are creating access to the object, you should be cautious to delete it at this point.
Me ? I don´t care about anyone else !
Well, let´s see if that is true. For this, the existing view sys.dm_sql_referenced_entities (MSDN) can be used. It will show all the objects that are being referenced by the passed in object. For our sample above, this would turn out to be:
You see that the referenced table itself as well as the used columns are mentioned in here.
So lonely here… Where have all the objects gone ?
Now you know how to find the referenced as well the the referencing objects. But what if I pull the rug / objects from under the referencing objects ? For schema bound objects (objects created WITH SCHEMABINDING) the answer is easy.. you can´t. But due to the restrictions you have with this option most of the objects in the real world are created "loosely" coupled without schema binding. If someone removes the referenced object from the referencing object, noone would ever know unless you execute / query the referencing object at runtime.
For a view you could find that easily using the sp_refreshview procedure. This will show you the following error:
But what about procedures, functions, triggers ? You can mark them for recompilation with sp_recompile, but this simply means that the query plan is thrown away and needs to be reevaluated before the next run.
Fortunately you have already learned one of the methods above to figure these things out. You can use the views mentioned above, as they will throw an error if any inconsistencies will be found. Wrapping this in a loop to go through all the objects as well as wrapping this into a TRY / CATCH block will get you all of the information you need.
At the end going through the views / procedures / functions and triggers, the script will output something similar on your databases:
Not only that the script will give you the Information about the objects that have wrong references, it will also give you the script to do a specific query finding the exact Information:
(The result)
(The Messages)
If you have any questions, let me know. The script is attached to the blog entry. Have fun and let me please know your results ! Looking forward to your experience with this ! Simply drop me a mail through the comment system or JensS(AtSymbolHere)Microsoft.com
-Jens