CODEWITHSUNDEEP
sql-server
user6465356
user6465356

Reputation:

how to Find a patricular table Table on a SQL Server across all Databases?

I want to find a table in SQL Server, let's say "XYZ". i don't know in which DB it is located. The server has many DBs

Upvotes: 0

Views: 52

Answers (2)

Jatin Patel
Jatin Patel

Reputation: 2104

This query will return a listing of all tables in all databases on a SQL instance: 

DECLARE @command varchar(1000) 
SELECT @command = 'USE ? SELECT name FROM sysobjects WHERE xtype = ''U'' AND Name LIKE ''%A'' ORDER BY name' 
EXEC sp_MSforeachdb @command

Upvotes: 0

MusicLovingIndianGirl
MusicLovingIndianGirl

Reputation: 5947

SELECT * FROM sys.Tables
WHERE
name LIKE '%XYZ%'

You can make use of SP sp_Msforeachdb - this will be executed on every single database you have in the server.

EXEC sp_Msforeachdb "USE [?]; SELECT '[?]' dbname, * 
FROM sys.tables
WHERE name like '%XYA%'"

Upvotes: 3

Related Questions