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?
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
I know you've already accepted an answer, but why not just use the much simpler sp_tables?
sp_tables 'Database_Name'
Try this:
USE your_database
go
Sp_tables
go
Try this
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE 'Datasheet%'
Try this :
select * from information_schema.columns
where table_name = 'yourTableName'
also look for other information_schema views.
And, since INFORMATION_SCHEMA is part of the SQL-92 standard, a good many databases support it - including MySQL.
Try following
SELECT table_name
FROM information_schema.tables
WHERE
table_name LIKE 'Datasheet%'
Try it :
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME LIKE '%'
One who doesn't know the TABLE NAME will not able to get the result as per the above answers.
TRY THIS
SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='dbo';
MS is slowly phasing out methods other than information_schema views. so for forward compatibility always use those.
I know this is an old question but I've just come across it.
Normally I would say access the information_schema.tables view, but on finding out the PDO can not access that database from a different data object I needed to find a different way. Looks like sp_tables 'Database_Name is a better way when using a non privileged user or PDO.
