{"id":97397,"date":"2023-07-31T18:07:47","date_gmt":"2023-07-31T18:07:47","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=97397"},"modified":"2026-04-16T09:58:00","modified_gmt":"2026-04-16T09:58:00","slug":"running-queries-across-many-sql-server-databases","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/running-queries-across-many-sql-server-databases\/","title":{"rendered":"Run T-SQL Across Multiple SQL Server Databases: A Reusable Solution"},"content":{"rendered":"

Running T-SQL across multiple databases on a SQL Server instance is a common DBA task – health checks, object searches, maintenance queries – but SQL Server has no reliable built-in tool for it. The sp_MSforeachdb system stored procedure is undocumented, inconsistent, and known to skip databases under certain conditions. <\/strong><\/p>\n

This article builds a reliable, reusable alternative: a T-SQL solution that creates a filtered database list from sys.databases, validates that required objects exist before executing on each database, and accepts an explicit database list parameter for ad-hoc use. The result is a framework DBA teams can deploy once and adapt for any multi-database query requirement.<\/strong><\/p>\n

A challenge that reappears periodically in the world of databases (especially database management) is the need to run code on a subset of databases and to do so in a nuanced manner. Some maintenance or metrics collection processes can be simply run against every database on a server with no ill-effect, but others may be app-specific, or need to omit specific sets of databases.<\/p>\n

This article dives into how to create and customize your own solution, tackling everything from filtering databases to validating schema elements to error-handling.<\/p>\n

What About sp_MSforeachdb?<\/h2>\n

The sp_MSforeachdb<\/code> system stored procedure can be used to run T-SQL across many SQL Server databases. While that sounds ideal, it is an undocumented black box that does not always perform the way you may want it to. Undocumented features can be used with extreme caution, but it is inadvisable to make them a key part of important processes as they may change, be deprecated, or be discontinued with little or no notice.<\/p>\n

In addition, sp_MSforeachdb<\/code> has no ability to be customized or expanded upon. What you see is what you get and there is no flexibility if it does not do precisely what you want.<\/p>\n

This article covers some of the things done in a procedure named dbo.RunQueryAcrossDatabases that you can download in a .zip file: RunQueryAcrossDatabases_FullScript.<\/a><\/em><\/p>\n

Code Reusability<\/h2>\n

The common solution to this problem is to create new code that iterates through databases for each application or process whenever it is needed. While this certainly is functional, it poses all of challenges of code maintainability when the same code is copied into many places.<\/p>\n

If a new database appears that is an exception the rules, then each and every process would need to be inspected and adjusted as needed to ensure that the exception is accounted for. Even on a small number of database servers, it is likely that one might be omitted and cause unexpected (and perhaps, not quickly noticed) harm. If hundreds of servers are being maintained, then the odds of missed many become quite high.<\/p>\n

A single universal stored procedure that is used for this purpose ensures that when changes are needed, they can be made a single time only:<\/p>\n

    \n
  • Update the stored proc in source control.<\/li>\n
  • Test it in dev\/QA.<\/li>\n
  • Deploy to production.<\/li>\n<\/ul>\n

    Because this process follows the typical one used for deploying application-related code, it is familiar and less likely to result in mistakes or omissions. In addition, a solution that is parameterized can ensure that when changes are needed, they can be made to parameter values and not to source code, minimizing the need for more impactful changes.<\/p>\n

    Building a Code Execution Solution<\/h2>\n

    To build a solution that executes T-SQL for us, there is value in listing the steps needed to accomplish this task:<\/p>\n

      \n
    • Create and apply filters to determine which databases should have code executed against them.<\/li>\n
    • Create code that will run against that database list.<\/li>\n
    • Iterate through that database list.<\/li>\n
    • Run the code against each of those databases.<\/li>\n<\/ul>\n

      When written out as a list, it becomes clear that this task is far simpler than it sounds, which means that our job of writing this code can move quickly through the necessities and into customization (which is far more fun!)<\/p>\n

      Note that a complete\/working solution is attached at the end of this article. Feel free to download and customize it to your heart’s content!<\/p>\n

      Create and Customize a Database List<\/h2>\n

      The key to this process is to create a list of databases that code will be run against. This may be as simple as an explicit list of N databases, but more often than not will involve some more detailed logic. For example, there may be a need to filter out all databases that do not meet a specific naming convention. Alternatively we may want to exclude databases by name.<\/p>\n

      Ultimately, this entire task comes down to querying sys.databases<\/code> and filtering based on information in the system view. Yeah, you heard that right: SELECT<\/code> from a view and add a WHERE<\/code> clause and DONE! OK, it isn’t quite that simple, but we’ll do our best to not make this complex. In order to create this process, dynamic T-SQL will be used. It is possible to do this via standard SQL by creating\/modifying a database list step-by-step, adding and removing databases along the way. While this certainly works, I find the resulting code even more convoluted than dynamic SQL (yes, I said that!).<\/p>\n

      As a brief reminder, sys.databases<\/code> is a system catalog view that provides a row per database on the server. Included in that data is a hefty amount of metadata and operational information that tells us how a database is configured and its current state. The following query returns some basic (but useful) information from this view:<\/p>\n

      SELECT\n\tdatabases.name,\n\tdatabases.database_id,\n\tdatabases.create_date,\n\tdatabases.compatibility_level,\n\tdatabases.collation_name,\n\tdatabases.user_access_desc,\n\tdatabases.state_desc,\n\tdatabases.recovery_model_desc\nFROM sys.databases;<\/pre>\n
      The results provide a whole lot of actionable info:<\/p>\n
      <\/p>\n
      When inspecting databases, knowing the compatibility level, collation, or current state could be critical to making decisions about whether or not to query them and especially apply changes to them. At a higher level, there is value in knowing this information regardless. For example, that BaseballStats <\/code>database of mine\u2026should that really be set to compatibility level 130 (SQL Server 2016)?! If that were an oversight, then it can be corrected. Similarly, should only two databases be using the FULL<\/code> recovery model? Another fine question for an administrator\/operator to consider.<\/p>\n
      Note that the database list above will be used for the duration of this article. The databases on your test server, as well as related objects and metadata will vary from what is presented here.<\/em><\/p>\n
      To start, let’s limit the database filtering to database name only. Additional customization based on other details is relatively easy to add once a starter-query has been constructed. To do this, a handful of variables will be introduced:<\/p>\n
      DECLARE @DatabaseNameLike VARCHAR(100) = 'WideWorldImporters';\nDECLARE @DatabaseNameNotLike VARCHAR(100);\nDECLARE @DatabaseNameEquals VARCHAR(100);<\/pre>\n
      These filters can be added one by one as filters to sys.databases<\/em>. If any filter is NULL, it can be ignored:<\/p>\n
      SELECT\n\t*\nFROM sys.databases\nWHERE (@DatabaseNameLike IS NULL \n       OR name LIKE '%' + @DatabaseNameLike + '%')\nAND (@DatabaseNameNotLike IS NULL \n       OR name NOT LIKE '%' + @DatabaseNameNotLike + '%')\nAND (@DatabaseNameEquals IS NULL OR name = @DatabaseNameEquals);<\/pre>\n
      If @DatabaseNameLike<\/code> is set to 'WideWorldImporters'<\/code> and the other variables are left NULL<\/code>, then the results of the above query would return:<\/p>\n
      <\/p>\n
      There are no limits to filtering like this. It would be relatively easy to add additional filters for names that start with a prefix, end in a suffix, or any other comparison that can be dreamed up.<\/p>\n
      Another common need is to omit system databases from queries. Running some T-SQL across many databases may include model<\/code>, master<\/code>, tempdb<\/code>, and msdb<\/code>, but oftentimes will not. This added filter to the query above can be handled by another variable and one more WHERE<\/code> clause:<\/p>\n
      DECLARE @DatabaseNameLike VARCHAR(100) = 'WideWorldImporters';\nDECLARE @DatabaseNameNotLike VARCHAR(100);\nDECLARE @DatabaseNameEquals VARCHAR(100);\nDECLARE @SystemDatabases BIT = 0;\nSELECT\n\t*\nFROM sys.databases\nWHERE (@DatabaseNameLike IS NULL \n     OR name LIKE '%' + @DatabaseNameLike + '%')\nAND (@DatabaseNameNotLike IS NULL \n     OR name NOT LIKE '%' + @DatabaseNameNotLike + '%')\nAND (@DatabaseNameEquals IS NULL \n     OR name = @DatabaseNameEquals)\nAND (@SystemDatabases = 1 \n     OR name NOT IN ('master', 'model', 'msdb', 'tempdb'));<\/pre>\n
      If a search is run using @DatabaseNameLike = 'B'<\/code> and @SystemDatabases = 1<\/code>, then the results will look like this:<\/p>\n
      <\/p>\n
      If the same search is performed, but with @SystemDatabases = 0, then the results are reduced to only a single result:<\/p>\n
      <\/p>\n
      Sys.databases<\/code> contains quite a few columns that can be filtered as easily as the name, such as compatibility level, collation name, user access (is it in single or multi-user?), state, recovery model, and much more. Adding filters can be accomplished on any column in the same fashion as above. For example, if there is a need to query all databases on a server – but only those that are online and multi-user, the query above could be adjusted as follows:<\/p>\n
      DECLARE @DatabaseNameLike VARCHAR(100) = 'WideWorldImporters';\nDECLARE @DatabaseNameNotLike VARCHAR(100);\nDECLARE @DatabaseNameEquals VARCHAR(100);\nDECLARE @SystemDatabases BIT = 0;\nDECLARE @CheckOnline BIT = 1;\nDECLARE @CheckMultiUser BIT = 1;\nSELECT\n\t*\nFROM sys.databases\nWHERE (@DatabaseNameLike IS NULL \n       OR name LIKE '%' + @DatabaseNameLike + '%')\n  AND (@DatabaseNameNotLike IS NULL \n       OR name NOT LIKE '%' + @DatabaseNameNotLike + '%')\nAND (@DatabaseNameEquals IS NULL \n     OR name = @DatabaseNameEquals)\nAND (@SystemDatabases = 1 \n     OR name NOT IN ('master', 'model', 'msdb', 'tempdb'))\nAND (@CheckOnline = 0 \n      OR state_desc = 'ONLINE')\nAND (@CheckMultiUser = 0 \n      OR user_access_desc = 'MULTI_USER');<\/pre>\n
      Run this query, and you will get back WideWorldImportersDW<\/code> and WideWorldImporters<\/code>. To test the offline change, let’s set WideWorldImportersDW<\/code> offline:<\/p>\n
      ALTER DATABASE WideWorldImportersDW SET OFFLINE;<\/pre>\n
      If you want to make it happen immediately on your local machine, you can add: WITH NO_WAIT ROLLBACK IMMEDIATE<\/code>; and it will kill existing transactions\/connections and apply the ALTER DATABASE<\/code> command.<\/p>\n
      Now, run the query and the offline database is removed from the results:<\/p>\n
      <\/p>\n
      We’ve dove into filtering the database list by metadata in sys.databases<\/code>. Next up is how to filter based on the existence (or non-existence) of objects.<\/p>\n
      Validating the Presence of Objects<\/h2>\n
      Another common challenge when executing a query across any number of databases is to only run the query if a specific table or object exists. Some examples of this include:<\/p>\n
        \n
      • Select data from a table, but only if it exists (or if a specific column in that table exists).<\/li>\n
      • Execute a stored procedure, but only if it exists. This prevents throwing \u201cObject Not Found\u201d errors.<\/li>\n
      • Validate if an object exists and log details about it, depending on its status.<\/li>\n
      • Check if a release completed successfully.<\/li>\n<\/ul>\n
        At its core, this is not a challenging task. There are many views available in SQL Server that allow us to check and see if an object exists or not. For example, the following code will check if the table Sales.Customers<\/em> exists in the WideWorldImporters<\/em> database:<\/p>\n
        SELECT\n      COUNT(*) AS DoesItExist\nFROM WideWorldImporters.sys.tables\nINNER JOIN WideWorldImporters.sys.schemas\nON tables.schema_id = schemas.schema_id\nWHERE tables.name = 'Customers'\nAND schemas.name = 'Sales';<\/pre>\n
        The result is straightforward, since there is one table name Sales.Customers<\/code> in the WideWorldImporters<\/code> database:<\/p>\n
        <\/p>\n
        What I want is for the logic behind this to be encapsulated into the process we are building. Having to rewrite this code every single time an object needs to be checked for is cumbersome, and someone will eventually make a mistake.<\/p>\n
        To get us started, two new parameters will be declared:<\/p>\n
        DECLARE @SchemaMustContainThisObject VARCHAR(100);\nDECLARE @SchemaCannotContainThisObject VARCHAR(100);<\/pre>\n
        When @SchemaMustContainThisObject<\/code> contains a value, then its presence will be validated and only databases that contain it will have the query executed against them. When @SchemaCannotContainThisObject<\/code> contains a value, then databases that contain that object will be excluded.<\/p>\n
        The following is a simple implementation of this logic, looking for the number of schemas that have a Customers<\/code> table in the WideWorldImporters<\/code> database:<\/p>\n
        DECLARE @SchemaMustContainThisObject VARCHAR(100) = 'Customers';\nDECLARE @SchemaCannotContainThisObject VARCHAR(100);\nDECLARE @DatabaseName sysname = 'WideWorldImporters'\nDECLARE @SQL NVARCHAR(MAX);\nDECLARE @ObjectValidationCount INT;\nDECLARE @ObjectExceptionCount INT;\nDECLARE @ParameterList NVARCHAR(MAX);\nIF @SchemaMustContainThisObject IS NOT NULL\nBEGIN\n     SELECT @SQL = 'SELECT @ObjectValidationCount = COUNT(*) FROM ' + \n               QUOTENAME(@DatabaseName) + --in case database name has \n                                          --spaces\/special characters\n                  '.sys.objects WHERE objects.name = ''' +  \n               @SchemaMustContainThisObject + ''';';\n     SELECT @ParameterList = '@ObjectValidationCount INT OUTPUT';\n     EXEC sp_executesql @SQL, \n                        @ParameterList, \n                        @ObjectValidationCount OUTPUT;\nEND;\nIF @SchemaCannotContainThisObject IS NOT NULL\nBEGIN\n     SELECT @SQL = 'SELECT @ObjectExceptionCount = COUNT(*) FROM ' + \n            QUOTENAME(@DatabaseName) + \n            '.sys.objects WHERE objects.name = ''' + \n            @SchemaCannotContainThisObject + ''';';\n     SELECT @ParameterList = '@ObjectExceptionCount INT OUTPUT';\n     EXEC sp_executesql @SQL, \n                        @ParameterList, \n                        @ObjectExceptionCount OUTPUT;\nEND;\nSELECT @ObjectValidationCount AS FoundObjectCount,\n       @ObjectExceptionCount AS NotFoundObjectCount;<\/pre>\n
        Executing this code, you will see that there are two schemas in the WideWorldImporters<\/code> have a Customers<\/code> object (the one we have been working with, and another a view object in the Website<\/code> schema).<\/p>\n
        Additional variables are declared to support parameterized dynamic SQL, as well as to store the output of the test queries. The counts can then be checked later to validate if an object exists or not. Note that sys.objects<\/code> is used here for convenience without any added object type checks. If additional criteria or object types need to be checked, adding checks on the type_desc<\/code> column in sys.objects<\/code> can be used to further filter object details and return exactly what you are looking for. If only tables are checked, then sys.tables<\/code> could be used instead. Similarly, if there is a need to check multiple types at once, additional variables\/parameters could be declared for different object types.<\/p>\n
        Providing an Explicit Database List<\/h2>\n
        A simple, but common implementation of this is to have an added parameter that provides an explicit database list to run a query against. If it is known exactly which databases need to be executed against and the list will not change without this code being altered, then it is possible to pass in a detailed list. This can be accomplished with a string or table-valued parameter, depending on your preference. Since the number of databases (and the length of this list) is bound by how many databases you have in a single place, it’s not likely that the length of this list would become prohibitively long for either solution. Therefore, the database list may be stored as a comma-separated-values string, like this:<\/p>\n
        DECLARE @DatabaseList VARCHAR(MAX);\nSELECT @DatabaseList = \n          'AdventureWorks2017, BaseballStats, WideWorldImporters';<\/pre>\n
        It may also be stored using a table-valued parameter:<\/p>\n
        CREATE TYPE dbo.DatabaseListType AS TABLE\n     (DatabaseName SYSNAME);\nGO\nDECLARE @DatabaseList dbo.DatabaseListType;<\/pre>\n
        Similarly, the table variable may be memory-optimized, if your SQL Server version supports it and you are so inclined:<\/p>\n
        CREATE TYPE dbo.DatabaseListType AS TABLE\n     (DatabaseName SYSNAME PRIMARY KEY NONCLUSTERED)\nWITH (MEMORY_OPTIMIZED = ON);\nGO\nDECLARE @DatabaseList dbo.DatabaseListType;<\/pre>\n
        If you choose that option, it is also worth considering using natively compiled stored procedures for consuming memory-optimized table variable. This is not an article about memory-optimized-awesomeness, and therefore a discussion of that will be skipped here. In either case, database names can be inserted into the user-defined table type like this:<\/p>\n
        INSERT INTO @DatabaseList\n\t(DatabaseName)\nVALUES\n     ('AdventureWorks2017'),\n     ('BaseballStats'),\n     ('WideWorldImporters');<\/pre>\n
        The final step is to prepare the T-SQL to execute and run it against the selected database list. A loop will be used to iterate through each database to run against. Assuming the query to execute is stored in the variable\/parameter @SQLCommand<\/code>, and the name of the current database to execute against is stored in @DatabaseName<\/code>, then the resulting execution code would look like this:<\/p>\n
        CREATE TABLE #DatabaseList\n\t(DatabaseName SYSNAME,\n\t IsProcessed BIT);\nINSERT INTO #DatabaseList\n\t(DatabaseName, IsProcessed)\n--<<<[Database List Determined Above and 0 for IsProcessed]>>>\nDECLARE @CurrentDatabaseName SYSNAME,\n        @SQLCommand nvarchar(max);\nWHILE EXISTS (SELECT * FROM #DatabaseList WHERE IsProcessed = 0)\nBEGIN\n\tSELECT TOP 1\n\t\t@CurrentDatabaseName = DatabaseName\n\tFROM #DatabaseList\n\tWHERE IsProcessed = 0;\n                            -- Replace \"?\" with the database name\n\tSELECT @SQLCommand = REPLACE(@SQLCommand, \n                                      '?', @CurrentDatabaseName); \n                           \n\tEXEC sp_executesql @SQLCommand;\n\tUPDATE #DatabaseList\n\t\tSET IsProcessed = 1\n\tWHERE DatabaseName = @CurrentDatabaseName;\nEND;\nSELECT *\nFROM   #DatabaseList;<\/pre>\n
        Executing this code using the table variable created earlier in the section, you will see that the databases you passed in will be processed (in this snippet of code, it will actually work whether the database exists or not)<\/p>\n
        There are many ways to write this code, so if your own implementation varies from this and works, then roll with it \ud83d\ude0a The only added feature in there is<\/code> to replace question marks with the database name. Note that if your query naturally contains question marks, then this may be problematic. This was added to mimic the behavior of sp_msforeachdb<\/code> and make<\/code> it easy to insert\/use the local database name for each database that is executed against in the loop. If your environment makes common use of question marks in code, then feel free to adjust it to a character or string that is more obscure\/deliberate.<\/p>\n
        Conclusion<\/h1>\n
        Building reusable tools is fun and can provide a great deal of utility in the future if more needs arise that require code like this<\/code>. A script containing all elements in this article is attached. Feel free to use it, customize it, and provide feedback if you think of new or interesting ways to tweak it.<\/p>\n
        There is value in avoiding use of undocumented SQL Server features in production environments. Fortunately for this tool, replacing it is not very complicated, and the new version can be given significantly more utility!<\/p>\n\n\n
        \n    
        FAQs: Running Queries Across Many SQL Server Databases<\/h2>\n\n                        
        1. Why shouldn't I use sp_MSforeachdb to run queries across all SQL Server databases?<\/h3>\n            
        \n                
        sp_MSforeachdb is an undocumented system stored procedure with known reliability issues: it can silently skip databases in certain conditions (particularly when databases are being created or dropped concurrently, or when names contain special characters), its behaviour is not guaranteed across SQL Server versions, and it provides no built-in filtering or error handling. For production use, a custom solution based on sys.databases with explicit cursor or while-loop iteration is more reliable and maintainable.<\/p>\n            <\/div>\n                    
        2. How do I run a query against all user databases in SQL Server?<\/h3>\n            
        \n                
        Build a filtered list from sys.databases: SELECT name FROM sys.databases WHERE database_id > 4 AND state_desc = ‘ONLINE’ AND is_read_only = 0. Iterate this list with a WHILE loop or cursor, building a dynamic SQL string that uses USE [database_name] or sets the database context via sp_executesql with a fully-qualified object name. Include object presence validation (checking INFORMATION_SCHEMA.TABLES or sys.objects within each database context) before executing the main query.<\/p>\n            <\/div>\n                    
        3. How do I only run a query on databases that contain a specific table?<\/h3>\n            
        \n                
        After building your database list, add a validation step inside the loop: before executing the main query against each database, use EXEC to check whether the target table exists in that database’s sys.objects. Build the existence check as a dynamic SQL string scoped to the current database context. If the table does not exist, skip that database and continue to the next. This prevents errors and avoids running queries against databases where the expected schema is absent.<\/p>\n            <\/div>\n                    
        4. Can I run a query across multiple SQL Server instances, not just databases?<\/h3>\n            
        \n                
        Yes, using SQL Server’s Central Management Server (CMS) feature in SSMS, which runs a single query against all registered server instances simultaneously. Alternatively, use PowerShell with the SqlServer module to iterate a list of instance names and call Invoke-Sqlcmd for each. For combining cross-instance and cross-database execution, PowerShell is the most flexible approach – connect to each instance, retrieve the database list from sys.databases, and run the target query per database.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"
        Run T-SQL across multiple SQL Server databases reliably – without sp_MSforeachdb. Build a reusable solution with database filters, object presence validation, and explicit database list support. Complete T-SQL included.…<\/p>\n","protected":false},"author":329827,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":true,"footnotes":""},"categories":[143527,53],"tags":[],"coauthors":[101655],"class_list":["post-97397","post","type-post","status-publish","format-standard","hentry","category-database-administration-sql-server","category-featured"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97397","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/329827"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=97397"}],"version-history":[{"count":7,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97397\/revisions"}],"predecessor-version":[{"id":109959,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/97397\/revisions\/109959"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=97397"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=97397"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=97397"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=97397"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}