Searching through a database via SQL

Imagine that while developing or testing a SQL Server database, you get an error message mentioning a database object, and you scratch your head, and mutter ‘by the bones of the saints, what is that?’. If it is a small database, then you can glance, maybe, at the browser pane. Once it gets to any size, you’ve got to search the metadata. If you know it is a column than you can look in information_schema.Columns or in sys.columns, but if you don’t know, or if you want to know more about the column, you’re into a certain difficulty. Someone has probably explained what the column does in an extended property. What table or view is it in? what else is in the table?

Finding things in databases isn’t always straightforward. You might think that you can just search the database objects in SQL Server. No, because user types, for example are stored elsewhere, despite being schema-bound. You might want to search in the scheduled jobs too, but there is no way of working out whether the code in a job step relates to a particular database. You might think that the information_schema represented a consistent industry standard, providing a rational logical layer over the seething reality underneath. You might be wrong.

SQL Server, like all RDBMSs, grow in rather strange way, sometimes in response to the esoteric needs of a major customer, the hobbyhorse of a senior manager, or to head off an industry-trend such as NoSQL or Big Data. We have to make retrospective sense of it all.

SQL Server has the concept of a parent object. This roughly corresponds to table, view and module (aka Routine). These parent objects have child objects, which correspond to constraints, and triggers. Internal tables can be either, depending on whether they are queue messages or XML Index nodes that do, or otherwise don’t.

Columns, indexes or parameters aren’t objects. They are treated as ordered lists of attributes to one particular type. Types are schema-based but they aren’t objects. Databases have attributes that aren’t schema-based but which apply to the database as a whole, such as datafiles or dataSpaces. You see some of them in the CREATE database statements in generated scripts.

All this might seem complicated, but I’ve done it for you. I’ve created two SQL Server routines. One is a Table-valued function that list database objects and their paths, and the other is a view that lists the database attributes as well.

You might use the schemaObjects function by itself if you aren’t interested in the database properties, just the database metadata. The obvious way of searching is this. Want to find out where the database processes credit cards?

 

…which gives you (in AdventureWorks) the following

Graphical user interface, text, application

Description automatically generated

The ‘path’ column gives you a reasonable sort order, except for the order of columns. It comes in handy for searching for a specific database object type, or for, say, the foreign key for a particular table.

Here we are trying to find out all the foreign keys in person.BusinessEntityAddress

 

Maybe you just want to see what is in a table

 

Graphical user interface, text

Description automatically generated

So here is the source of the application. If it is updated, the latest version will be in the Gloop project TheGloopSQLServerDatabaseDocumenter/SearchDatabase.sql

 

The view adds the attributes at database level. Of course, there aren’t going to be many of these in a sample database but here goes

Select * from The_Metadata where path like ‘$.Attributes%’

Graphical user interface, text, application

Description automatically generated

 

Conclusion

If you are like me, you will tend to do ad-hoc searching via SQL Calls that are written for the immediate purpose. They aren’t always good for their purpose and I weas inspired to write this so as to be able to do path-based searches and to be able to see the comments (extended properties.) It is based various routines I’d needed for another purpose so it is good to pull it all into shape for this function and view.