sys.objects And You: Finding Rogue Objects In Your Database
Whenever I'm creating a new database, my tables, procedures, constraints, and other objects usually undergo significant refactoring as part of the development process. I've found that a good pattern to use for maintaining database objects - especially tables where lots of keys are used - looks something like this:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.SomeTableName') AND TYPE IN (N'U')) DROP TABLE dbo.SomeTableName CREATE TABLE dbo.SomeTableName ( -- table definition here )
When I create a new object, I make sure to drop it if it exists, then I re-create it. This way, I can run my script over and over and over and every time, it will put the database into a known state with all the objects I need.
This works great, but what if I'm refactoring and I end up eliminating a table from my schema? If I don't remember to stop and DROP it right then, it's not going to be DROPped when I re-run my script, because when I remove the code to CREATE the table, I remove the code to DROP it as well.
So, in effect, I end up with these rogue tables with no key relationships to anything that remain undetected until several months later, when I'm working on something and they accidentally show up as Intellisense suggestions. "Did I delete that? I don't remember if I deleted that or not... why is this still here?"
As it turns out, there's a really easy way to query sys.objects to see if there are any user-created objects in the current database:
SELECT * FROM sys.objects WHERE is_ms_shipped = 0
That's it. is_ms_shipped is just a BIT column in sys.objects - if it's set to 0, that just means it's not a built-in object.
I discovered this the other day when I was trying to make sure that I had cleared all user-created objects out of a new database as part of the testing process. At work, we use separate 'backout' scripts as part of SQL projects to clear out all the objects we've created to ensure that when the code is promoted to production, nothing will be missing. Since I'm creating a new database for this particular project, at the end of my backout script, I have an IF statement that checks the COUNT() of all records in sys.objects where is_ms_shipped is set to 0 - if it's greater than zero, that means I missed something. So far it's been a great way to make sure that my code is behaving as expected.
