Solved

SQL Server: need a list of tables with certain name

Posted on 2014-02-11
2
448 Views
Last Modified: 2014-02-26
Hi, what is the best way to get a list of tables with name like '%name1%' from all server instance databases?
Thanks.
0
Comment
Question by:quasar_ee
2 Comments
 
LVL 22

Accepted Solution

by:
Steve Wales earned 250 total points
ID: 39851900
May not be the most elegant way to do it but for a quick and dirty use the undocumented procedure sp_MSForEachDB:

EXEC dbo.sp_MSforeachdb 'USE [?];  IF EXISTS (SELECT * FROM  sys.tables WHERE name like ''%name1%'' ) select ''Table ''+name+'' exists in database ''+DB_NAME() from sys.tables where name like ''%name1%''';

Open in new window

0
 
LVL 5

Assisted Solution

by:dannygonzalez09
dannygonzalez09 earned 250 total points
ID: 39851913
give this a try

sp_msforeachdb 'select "?" AS db, * from [?].sys.tables where name like ''%name1%'''
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question