Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 483
  • Last Modified:

SQL Server: need a list of tables with certain name

Hi, what is the best way to get a list of tables with name like '%name1%' from all server instance databases?
Thanks.
0
quasar_ee
Asked:
quasar_ee
2 Solutions
 
Steve WalesSenior Database AdministratorCommented:
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
 
dannygonzalez09Commented:
give this a try

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now