Script to Search Through all Databases for a Table Name

Question

Script to Search Through all Databases for a Table Name

Sibandaz

Right there with Babe

Points: 733
More actions
June 23, 2012 at 1:07 pm

#271678

Comments posted to this topic are about the item Script to Search Through all Databases for a Table Name

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

markwiddowson Valued Member Points: 55 More actions · Answer 1

A similar result may be achieved using the undocumented stored procedure sp_MSforeachdb:

EXECUTE master.sys.sp_MSforeachdb 'USE [?]; SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name from [?].INFORMATION_SCHEMA.TABLES where table_type = ''BASE TABLE'' AND TABLE_NAME LIKE ''%Sales.Cust%'''

G-Dub Mr or Mrs. 500 Points: 504 More actions · Answer 2

Microsoft included a stored procedure with SQL Server 2000 called "sp_MSObjSearch" that did exactly what you're proposing to do. It's parameterized so that you can choose what type of object to search for.

If you have an instance of SQL 2000 available, you can get view the code by executing sp_helptext sp_MSObjSearch.

This procedure is incredibly helpful, and I'm mystified as to why MS stopped including it in the basic server installation. I've seen the procedure deployed successfully in SQL 2005 and SQL 2008 instances.

Sibandaz Right there with Babe Points: 733 More actions · Answer 3

Thanks very much but I dont have sql 2000. I am using 2005 and 2008.

Cadavre SSC-Forever Points: 41690 More actions · Answer 4

Would this not achieve the same thing?

DECLARE @sql NVARCHAR(MAX), @SEARCH_STRING NVARCHAR(100) = '%TB_DIM%';

SELECT @sql = STUFF(sqlCode.value('.', 'varchar(max)'),1,10,'') + ';'

FROM (SELECT 'UNION ALL SELECT table_catalog + '+CHAR(39)+'.'+CHAR(39)+' + table_name ' + CHAR(13) + CHAR(10) +

'FROM ' + QUOTENAME(Name) + '.INFORMATION_SCHEMA.TABLES '+ CHAR(13) + CHAR(10) +

'WHERE table_type = ' + CHAR(39) + 'BASE TABLE' + CHAR(39) + ' AND table_name LIKE ' +

CHAR(39) + @SEARCH_STRING + CHAR(39) + CHAR(13) + CHAR(10)

FROM sys.databases

WHERE database_id > 4

FOR XML PATH(''), TYPE) a(sqlCode);

PRINT @sql;

EXECUTE sp_executesql @sql;

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Sunny1 SSC Eights! Points: 871 More actions · Answer 5

I really love a FREE tool - SQL Search from RED Gate - http://www.red-gate.com/products/sql-development/sql-search/.

It seaches pretty much any DB subject, table, view, store proc, columns, etc., very fast. Thought you maybe interested.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

We just made a similar script but focusing on the columns and not using the schemas (as they don't use them in here).

But because we're not dbas and we don't have access to all databases, our query at sys.databases had a couple of extra filters to avoid errors.

AND state_desc = 'ONLINE'

AND HAS_DBACCESS (name) = 1

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

akljfhnlaflkj SSC Guru Points: 76202 More actions · Answer 7

akljfhnlaflkj

SSC Guru

Points: 76202

May 10, 2016 at 9:31 am

#1877869

Thanks for the script.