CODEWITHSUNDEEP
sql-serverdatabase
star_kid
star_kid

Reputation: 191

How to find a table in sql server if only the partial table name is known

I have a sql server database which contains 7000 tables and every table ends with a date. I need to find out all the tables of a particular date.

I have the table names as follows:

HouseKeeping_Stage1_12_6_14,
HouseKeeping_Stage1_13_6_14,
HouseKeeping_Stage1_14_6_14,
HouseKeeping_Stage2_12_6_14,
HouseKeeping_Stage2_13_6_14

I want to find out all the records which are associated with the date 12_6_14.

Please let me know how to write the query.

Upvotes: 1

Views: 8267

Answers (2)

Dan Guzman
Dan Guzman

Reputation: 46213

Using SSMS Object Explorer, right-click on the Tables node and select Filter-->Filter Settings. The list will be filtered accordingly.

Alternatively, you can query the catalog or INFORMATION_SCHEMA views:

SELECT 
      OBJECT_SCHEMA_NAME(object_id) AS schema_name
    , name
FROM sys.tables
WHERE name LIKE '%[_]13[_]6[_]14%';

SELECT 
      TABLE_SCHEMA
    , TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%[_]13[_]6[_]14%';

Upvotes: 3

Circadian
Circadian

Reputation: 579

This should do the trick: 

SELECT * FROM information_schema.tables
where table_name like '%12_6_14%'

Here is an example: http://sqlfiddle.com/#!6/304bd/1/0

Upvotes: 2

Related Questions