At a higher level, there is value in knowing this information regardless. 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. The results provide a whole lot of actionable info: The following query returns some basic (but useful) information from this view: 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. While this certainly works, I find the resulting code even more convoluted than dynamic SQL (yes, I said that!).Īs a brief reminder, sys.databases is a system catalog view that provides a row per database on the server. 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. In order to create this process, dynamic T-SQL will be used. Yeah, you heard that right: SELECT from a view and add a WHERE clause and DONE! OK, it isn’t quite that simple, but we’ll do our best to not make this complex. Ultimately, this entire task comes down to querying sys.databases and filtering based on information in the system view. Alternatively we may want to exclude databases by name. For example, there may be a need to filter out all databases that do not meet a specific naming convention. This may be as simple as an explicit list of N databases, but more often than not will involve some more detailed logic. The key to this process is to create a list of databases that code will be run against. Feel free to download and customize it to your heart’s content! Create and Customize a Database List Note that a complete/working solution is attached at the end of this article. 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!) Run the code against each of those databases.Create code that will run against that database list.Create and apply filters to determine which databases should have code executed against them.To build a solution that executes T-SQL for us, there is value in listing the steps needed to accomplish this task: 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. Update the stored proc in source control.īecause this process follows the typical one used for deploying application-related code, it is familiar and less likely to result in mistakes or omissions.If hundreds of servers are being maintained, then the odds of missed many become quite high.Ī single universal stored procedure that is used for this purpose ensures that when changes are needed, they can be made a single time only: 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 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. While this certainly is functional, it poses all of challenges of code maintainability when the same code is copied into many places. The common solution to this problem is to create new code that iterates through databases for each application or process whenever it is needed. zip file: RunQueryAcrossDatabases_FullScript. This article covers some of the things done in a procedure named dbo.RunQueryAcrossDatabases that you can download in a. What you see is what you get and there is no flexibility if it does not do precisely what you want. In addition, sp_MSforeachdb has no ability to be customized or expanded upon. 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. While that sounds ideal, it is an undocumented black box that does not always perform the way you may want it to. The sp_MSforeachdb system stored procedure can be used to run T-SQL across many SQL Server databases. This article dives into how to create and customize your own solution, tackling everything from filtering databases to validating schema elements to error-handling. 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. 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.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |