How to get a list of objects from all databases on a SQL Server host

SQL Server has a very complete metadata catalog, which comes in handy a lot more often than you might think. One limitation of it though, is that all the metadata is stored on a per-database basis, which makes it difficult to correlate information between databases. (The metadata is stored in system tables in each individual database.)

Last week, I needed to gather a list of stored procedures from multiple databases. I found some solutions online, but most of them involved spitting out multiple results sets, which wasn't particularly useful for me.  After poking around for a little while, I came up with the following solution:

USE [master]
GO

DECLARE @SchemaName VARCHAR(50) = 'dbo';
DECLARE @sql AS VARCHAR(MAX) = '';

SELECT 
 @sql = @sql +
 'SELECT ''['+name+'].['+@SchemaName+'].'' + name AS procedure_name ' +
 'FROM ['+name+'].sys.procedures ' + 
 'WHERE schema_id = '+ 
  '(SELECT schema_id ' +
  ' FROM ['+name+'].sys.schemas ' + 
  ' WHERE name = '''+@SchemaName+''') ' +
 'UNION ' + CHAR(10)
FROM sys.databases
WHERE [state] = 0

SET @sql = LEFT(@sql, LEN(@sql)-8)

EXEC(@sql)
GO

(The sys.databases table is available in every database, not just master. Operating out of a system database just seemed to make more logical sense since the whole point is to gather data from 'user' databases.)

Essentially, what this is doing is generating the following statement for each database:

SELECT '[db_name].[dbo].' + name AS [procedure_name]
FROM sys.procedures 
WHERE schema_id =  
 (SELECT schema_id 
  FROM sys.schemas
  WHERE name = 'dbo')

and then UNION-ing all the results together. It involves a little 'magic' in that @sql = @sql + (stuff) statement, which basically makes it so each row emitted by the SELECT statement adds to the value of @sql (which is why it must be pre-populated as '' instead of NULL.)

I want to emphasize that dynamic SQL like this is risky - like any other kind of SQL statement building, it opens you up to SQL injection attacks, even if contained in a stored procedure. As a script that you store on your machine and run ad hoc though I think it's a good solution.

Complex .NET config transformations

In my previous post, I talked about (web).config transformations: how great they are and how they can be enhanced beyond the 'base install.' Continuing on, some of those enhancements later led us to some even more useful and advanced actions.

Using multiple configuration files in a .NET project is not the simplest thing to achieve. There are different mechanisms for including/merging files, but each have their limitations. One way is to specify that a section comes from another file, like so:

<connectionStrings configSource="otherfile.config"/>

The limitation with this approach is that the entire section must come from that other file. There is no 'merging' of elements; you couldn't add any additional elements to the <connectionStrings> section in this scenario.

You can get around this by importing the other config this way instead:

<appSettings file="otherfile.config">
   <add key="PagesToHide" value="AdminPage" />
   <add key="ExternalLinksToHide" value="Utilization Report" />
</appSettings>

The limitation with this approach is that this kind of import is not supported for all types of config sections:  <system.servicemodel>, for example, can't be used this way. (Also, some code inspection tools, like ReSharper, don't know how to parse this syntax.) And both these approaches force the imported file to be only that one config section; you couldn't have an <appSettings> section and a <connectionStrings> section in an external file and merge/import them both into your config.

In some scenarios, these limitations are not a problem. Both of these approaches have served us well (on a limited basis) in the past. Recently however we had the need to include a non-trivial set of configuration values into the config of multiple applications, most of which were already using web.config transformations. I generally assume that, as a developer, if I have copied and pasted something then I have failed. I wanted to find a more elegant, maintainable solution than just making each development team copy and paste the values into their individual base and transformation configs.

The solution we eventually came up involves a multi-step transformation. Don't be daunted by that "multi" though - it's actually quite a simple implementation.

We started by renaming the web.config (and its children web.debug.config & web.release.config) to web.base.config (children: web.base.debug.config & web.base.release.config). The content of these files we left untouched. We then added the files gateway.debug.config and gateway.release.config to the project. Each one looked something like this:

<?xml version="1.0" encoding="utf-8" ?>
<configuration xmlns:xdt="http://schemas.microsoft.com/XML-Document-Transform">
  <appSettings>
    <add key="Thumbprint" 
         value="52CD92D192786742DA589FEA4C83719DA43E82C9" 
         xdt:Transform="Insert" />
    <add key="Version" 
         value="4" 
         xdt:Transform="Insert" />
    <add key="ConnectionString" 
         value="Data Source=db_host;Integrated Security=True" 
         xdt:Transform="Insert" />
  </appSettings>
  <system.serviceModel>
    <bindings>
      <basicHttpBinding>
        <binding name="BasicHttpBinding_Gateway"
                 closeTimeout="00:00:10" 
                 openTimeout="00:00:10" 
                 receiveTimeout="00:00:10" 
                 sendTimeout="00:00:10"
                 xdt:Transform="Insert">
          <security mode="Transport" />
        </binding>
      </basicHttpBinding>
      <wsHttpBinding>
        <binding name="WsHttpBinding_Gateway"
                 closeTimeout="00:00:10" 
                 openTimeout="00:00:10" 
                 receiveTimeout="00:00:10" 
                 sendTimeout="00:00:10"
                 xdt:Transform="Insert">
          <security mode="Transport">
            <transport clientCredentialType="Windows"
                       proxyCredentialType="None"
                       realm="" />
            <message clientCredentialType="Windows"
                     negotiateServiceCredential="true" />
          </security>
        </binding>
      </wsHttpBinding>
    </bindings>
    <client>
      <endpoint address="https://fake.url/GatewayService/4/a.svc/basic"
          binding="basicHttpBinding" 
          bindingConfiguration="BasicHttpBinding_Gateway"
          contract="namespace.interface"
          name="GatewayTransport_1"
          xdt:Transform="Insert" />
      <endpoint address="https://fake.url/GatewayService/4/a.svc/roles"
          binding="wsHttpBinding" 
          bindingConfiguration="WsHttpBinding_Gateway"
          contract="namespace.interface2"
          name="GatewayRoles_1" 
          xdt:Transform="Insert" />
    </client>
  </system.serviceModel>
</configuration>

The important parts to notice here are the xdt:Transform="Insert" attributes in each XML node. With XML transformations, it's possible to add (insert) elements as well as modifying and deleting them. (The xmlns attribute in the <configuration> element is also very important; without it Visual Studio doesn't know the file is a transformation document.)

The 'magic' comes in with a build step added to the project file:

<Target Name="BeforeBuild">
   <TransformXml 
       Source="web.base.config"
       Transform="Gateway.$(Configuration).config"
       Destination="obj\$(Configuration)\web.intermediate.config" />
   <TransformXml 
      Source="obj\$(Configuration)\web.intermediate.config"
      Transform="web.base.$(Configuration).config"
      Destination="web.config" />
</Target>

Now when the project builds, the compiler takes the base config and transforms it with the appropriate gateway config. Since the gateway config only has addition transformations, this effectively works like a merge of the two files. Then, the build configuration-specific transformation is performed, updating the elements that originally came from the base config to their build-appropriate values. This produces the web.config for the build configuration the solution was built in.

Like approaches discussed in the previous article, this solution is very seamless because it happens at compile-time, so you're able to validate the result at any point, and the web.config that IIS wants is always there. Plus, the gateway configs could be dropped into any project with ease because they just add on to what's already there. The one drawback is that you do have to rebuild to get IIS to pick up configuration file changes, whereas usually you can just save the file and refresh the web page. (This also makes the Visual Studio context menu item 'add config transform' not work, but then it doesn't always work anyway, and adding files to a project is a pretty trivial task.)

The example used here is for a web.config, but this should all work exactly the same with an app.config (so long as the build task is imported - see previous article.)

The possibilities presented by this technique are endless - you could do some pretty complex and powerful things by chaining transformation steps together. You don't want to have too many config files, but if you need to bring separation of concerns or just better readability to your application configuration, this is a powerful and elegant way to do it.