Execute an sql statement at all database of an sql server instanct.Posted: 19/12/2017
In this post I will write about a problem I had. I needed to find a value in a specific name of a client. I have many similar databases but the data in the databases differ. So, I needed to execute a SQL statement at every database to find the right value. After some searching I found the sp_msforeachtable system stored procedure. With this procedure you can execute SQL code at every database at you SQL server instance.
Here are the examples I needed to find the customer with the customerId 2 in all databases.
--select customerId in the customer table at every database at the sql server instance DECLARE @command varchar(1000) SELECT @command = 'USE ? SELECT DB_NAME() as DBName, * FROM Customers where CustomerId = 2'; EXEC sp_MSforeachdb @command GO
Here you see the result, I found it in two databases (master and Preparation).
After I solved that problem I tried some different things with the sp_MSforeachdb procedure. In the next example I select all databases with name, filepath and size of the file.
--select all databases with name, filename and size DECLARE @command varchar(1000) SELECT @command = 'USE ? SELECT DB_NAME() as DBName, SF.Name as Name, SF.filename as Filename, SF.size as Size FROM sys.sysfiles SF' EXEC sp_MSforeachdb @command GO
Here you see the result of my SQL server instance.
After that i tried to select all tables in all databases that start with an p. As you see u can use the ? as an placeholder for the database name of the actual database.
--select all tables that start with p DECLARE @command varchar(1000) SELECT @command = 'USE ? SELECT DB_NAME() as DBName, '' '' as Name, '' '' as xtype UNION SELECT DB_NAME() DBName, Name, xtype FROM SYSOBJECTS WHERE xtype = ''U'';' EXEC sp_MSforeachdb @command GO
Then I thought about what I could do with that procedure and while searching the internet for further good examples I discovered the sp_MSforeachtable procedure. That procedure does not execute the command for every database it executes the command for every table in the actual database. That means the ? is the placeholder for the tablename. Here are some examples what you can do with them.
--select the number of rows in every table EXEC sp_MSforeachtable 'Select ''?'' as tableName, count(1) numRecords from ?';
Two further idears to use the sp_MSforeachtable procedure are to rebuild the index of every table in the database and to update the statistics for all tables at the database.
--update all statistics in all Tables in a database EXEC sp_MSforeachtable 'update statistics ? with all';
--rebuild all indexes for all tables in a database EXEC sp_MSforeachtable 'print ''?'' DBCC DBREINDEX(''?'','''',80)';