Tag Archives: sp_MSForEachDB

How to: Find a Table on a SQL Server across all Databases

Some times it is difficult to find a table name from a SQL instance that hosted tremendous database.  “Which database I suppose to search for?” is the question that often pop-up in DBA’s mind and they hope the user can give them the database name as well.  Sadly speaking in most cases user do not know which database their application connect to.

To find a table in the SQL across all databases you can use undocumented stored procedure sp_MSForEachDB.

sp_MSforeachdb 'SELECT "?" AS DB, * FROM [?].sys.tables WHERE name like ''%your_table_name%'''

Shrink All Logfiles

If you are looking for a script to shrink all transaction log of the database, you come to the right place. The following scripts make use of the “sp_MSForEachDB”, which is a undocumented store procedure found in SQL Server 2005 and above.

USE [DatabaseName]
GO
EXEC sp_MSforeachdb 'DECLARE @ln varchar(100) SET @ln=(SELECT name FROM [?].dbo.sysfiles WHERE fileid=2) USE [?] DBCC SHRINKFILE (@ln, 0)'

References: