• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 481
  • 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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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