locate which database a view, table, sp belongs to 1

[R7Dave]

Hello

In SQL2005, is there a tool or T-SQL statement I can run that will let me know what database a table, or view, stored procedure belongs to.

Thanks
Dave

 

[RiverGuy]

Does this help?

DECLARE @ObjectToSearchFor VARCHAR(200)
SELECT @ObjectToSearchFor = 'NameOfSomeTable'

SELECT TOP 0 * INTO ##TablesAndViews FROM INFORMATION_SCHEMA.TABLES 
SELECT TOP 0 * INTO ##Routines FROM INFORMATION_SCHEMA.ROUTINES
	
EXEC Master..sp_msforeachdb 'INSERT INTO ##TablesAndViews SELECT * FROM ?.INFORMATION_SCHEMA.TABLES; INSERT INTO ##Routines SELECT * FROM ?.INFORMATION_SCHEMA.ROUTINES'

SELECT * FROM ##TablesAndViews WHERE TABLE_NAME = @ObjectToSearchFor 
SELECT * FROM ##Routines WHERE ROUTINE_NAME = @ObjectToSearchFor 
DROP TABLE ##TablesAndViews
DROP TABLE ##Routines

 

[R7Dave]

very nice - yes it does - thank you!