Reflective SQL Queries

How to search a SQL Server to find names of database objects such as Tables, Views, Stored Procedures and their columns and parameters.

As a consultant, I have found it important to be able to navigate quickly through unfamiliar, large and complicated SQL Server (Sequel) databases. There is rarely useful accompanying documentation.

When documentation exists, it is generally: sparse, undescriptive, and out of date. Even if there were a complete document, it would probably be inefficient at answering many questions.
Having good reflective database tools is instrumental in meaningful and definitive answering many database questions. For instance, let’s say you find in your code a name of a column. A couple of questions may arise:

  • What is a list of columns in the database with this name?
  • What is the corresponding data type?
  • Is it a required field?
  • Can I get an idea of what the values look like in this column?
  • What are some of the other column siblings and the corresponding field types?

I have found that that having some key reflective Queries will help you find things more quickly when finding Tables, Views, Stored Procedures, etc. … I was also able to answer questions for others who have more experience with the database than I am.
The queries below will provide you a simple to use way to discover the objects for a target database.

  • The queries below are extremely easy to use, just copy and paste.
  • The query provided below have been tested in MS SQL 2008 and after. It may also work in 2005.
  • This query is only a starting point. You want to be especially careful of the context in reviewing the definition. If your query is not written correctly, it may erroneously include or exclude procedure objects.
  • You need to have appropriate permissions.
  • You will only be able to query unencrypted SLQ procedure objects.
    • You will not be able to query encrypted procedure objects.
    • You will not be able to query CLR procedure objects.
  • As always, remember to be careful in running any query, including this one, in a production environment.

Provides a list of database object fields: newayva.com/object-field-query/
Provides a list of database table columns: newayva.com/table-column-query/
Simple query to retrieve key table information. Name, schema, how many columns, and which columns (in Xml).newayva.com/table-query/

How to search the definition of SQL stored procedures, stored functions and triggers.
The Procedure Definition Query, while less precise, is still a very useful query. This query will should give you a starting point to isolate procedures of interest. Where I have used this query before is discovering: unique punctuation like the # which indicate a temporary table, hardcoded strings, or parts of object names. By using this query, I have been able to speed up my development process, reduce process (or business logic) duplication, and isolate or prevent bugs.

Queries procedures, functions and triggers including their definition. This can be used if the dependency tools provided in the Management Studio do not provide the appropriate results: newayva.com/procedure-definition-query/

How to search a database for a given string or GUID.

In some systems it can be more difficult to find where the model content is stored in the database. There are numerous reasons for this scenario occurring, which all boil down to the data layer is obscured / convoluted or we are making bad assumptions. Additionally, sometimes it isn’t hard to see how data is located, but it can take a lot of time. This got me thinking of, wouldn’t it be nice if I could be able to search a database for a given value.

I think you will find that using this Table String Search Procedure is can help you validate a number of assumptions. If you are working in a DEV environment, you can insert some sort of unique string (test value) would be distinct from other values in the database. For instance, assuming you are not working with produce, you could use the string watermelon. When you have located a particular field in a table, you could validate that other fields are persisting as expected.

Searches for a provided string stored in a database: newayva.com/table-guid-search-procedure/

Additionally, when working with certain CMS(s) which heavily use GUID(s), the Table GUID Search Procedure may be helpful in piecing together related rows.

Searches for a provided GUID stored in a database: newayva.com/table-string-search-procedure/

How to search a database for table row counts.

The Table Row Count Procedure can also be used in a couple of different ways. If you run this query against two different database instances for essentially the same application, you can have a rough metric to be able to determine differences. This will obviously not tell you if the contained data is different, but it can be a simple indicator of changes. The second place that I was able to use this is to determining a unit of work. I was working with a new CMS and was trying to determine which tables had new rows after I performed a particular action. What it did not tell me is if any existing rows were modified. However, by seeing row count differences, it did provide me with more information that I had before. It should be noted that this kind of a use case also assumes that you as the developer are working in an isolated database environment, or your results will be skewed.

Provides a list of tables and their respective row counts: newayva.com/table-row-count/

Additional thoughts.

In addition to some of these queries, I also strongly recommend looking at the redgate tools. They are very helpful in comparing database schema and data (between development and production). The redgate tool set is somewhat pricy, but you should always balance the cost of the product against the labor saved. I think of it as a must for managing deployments. It can also help in data migration as well as provide source control for stored procedures. Visual Studio can do this as well, but there are limitations that you should be aware of as you are providing a strategy for your solution.

When possible, providing simple, meaningful names can greatly reduce the complexity of any database; the more regular, the better. Even though you may not always have control over this. Look for a blog in the near future for Database Development best practices.

Finally consider testability when designing your data structures. A key point, some will argue that using procedures, functions, and triggers should be avoided. With large designs, however, this may impact performance. What is absolutely vital is having testability in mind, no matter where the business logic resides.