SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



How to Find a SQL Table in All Different Databases on a SQL Server Instance using sp_Msforeachdb

SQL Server database administrators sometimes need to search for a sql table in all sql databases on a SQL Server instance.

Assume that you have a MS SQL Server 2008 instance installed on a database server. On this SQL Server instance, you have about 25 databases. And you wonder in which sql databases, a sql table with table name "Customer" exists. One way of learning the SQL Server databases which has the Customer sql table, is connecting to all databases in order and running the below simple sql SELECT query.

SELECT * FROM sys.tables WHERE name = 'Customer'
SELECT * FROM sysobjects WHERE name = 'Customer' AND xtype = 'U'

sysobjects is an old sql system view. Sys.Table is new with SQL Server 2005 and works at SQL Server 2008 databases. But sql developers and database administrators can execute the first SELECT statement on MS SQL Server 2000 database instances.

But not event a single SQL Server database administrator will want to connect to each of the installed databases on that instance in order and execute the SELECT statement one by one.
Because there is a more simple sql SELECT query which will run once and will work on every sql database on the SQL Server instance.

Using sql sp_Msforeachdb undocumented stored procedure, the SELECT statement or any t-sql statement passed as an argument will be executed on every SQL database existing on the SQL Server instance.

EXEC sp_Msforeachdb "USE [?]; SELECT '[?]' dbname, * FROM sys.tables WHERE name = 'customer'"

The above sp_Msforeachdb will return the database name and the table entry for Customer table for databases where Customer sql table exists.






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems