How can I do the equivalent of "SHOW TABLES" in T-SQL?

Question

I would like to do a lookup of tables in my SQL Server 2005 Express database based on table name. In MySQL I would use 'SHOW TABLES LIKE "Datasheet%"', but in T-SQL this throws an error (it tries to look for a SHOW stored procedure and fails).

Is this possible, and if so, how?

Answer 1

select * from information_schema.columns where table_name = 'yourTableName'

also look for other information_schema views

Answer 2

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'Datasheet%'

Answer 3

SELECT table_name FROM information_schema.tables WHERE table_name LIKE 'Datasheet%'

Answer 4

This will give you a list of the tables in the current database:

Select Table_name as "Table name"
From Information_schema.Tables
Where Table_type = 'BASE TABLE' and Objectproperty 
(Object_id(Table_name), 'IsMsShipped') = 0

Some other useful T-SQL bits can be found here: http://www.devx.com/tips/Tip/28529

Answer 5

I know you've already accepted an answer, but why not just use the much simpler sp_tables?

sp_tables 'Database_Name'

Answer 6

well... MS is slowly phasing out methods other than information_schema views. so for forward compatibility always use those.

Answer 7

And, since INFORMATION_SCHEMA is part of the SQL-92 standard, a good many databases support it - including MySQL.

Answer 8

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%'

Try it!!!!