Find all tables whose name ends with a certain suffix

sql sql-server sql-server-2008 select information-schema
Shree picture Shree · Jan 9, 2014 · Viewed 35.5k times · Source

I have thousand of tables in database. Some names end with _History.

For example :

abc_History
bcd_History
123_History

How do I find all tables which name is end with _History.

Some thing like: 

SELECT
table_name
FROM sys.tables WHERE table_name LIKE '_History%'

And 

error : Invalid column name 'table_name'.

Answer

Saharsh Shah picture Saharsh Shah · Jan 9, 2014

Try this: 

SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.tables 
WHERE TABLE_NAME LIKE '%_History'

OR

SELECT name
FROM sys.tables
WHERE name LIKE '%_History'

Related questions

Total Row Count in sql query---sql server 2008

My query is as follows BEGIN WITH MyCTE AS ( SELECT T.MusicAlbumTitle ,D.musicTitle ,D.mVideoID ,D.musicFileName ,T.ReleaseDate AS ReleasedDate ,D.MusicLength ,D.musicSinger ,D.MusicVideoID ,D.ExternalLink ,D.CoverImg ,ROW_NUMBER() OVER ( PARTITION BY D.MusicVideoID ORDER …

Read More
How can I get column names from a table in SQL Server?

I want to query the name of all columns of a table. I found how to do this in: Oracle MySQL PostgreSQL But I also need to know: how can this be done in Microsoft SQL Server (2008 in my case)?

Read More
How to Delete using INNER JOIN with SQL Server?

I want to delete using INNER JOIN in SQL Server 2008. But I get this error: Msg 156, Level 15, State 1, Line 15 Incorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' …

Read More