如何在T-SQL中执行“SHOW TABLES”的等效操作？

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).

我想基于表名在SQL Server 2005 Express数据库中查找表。在MySQL中我会使用SHOW TABLES LIKE“数据表％”，但在T-SQL中会抛出一个错误（它试图查找SHOW存储过程并失败）。

Is this possible, and if so, how?

这是可能的，如果是的话，怎么样？

10 个解决方案

#1

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

其他一些有用的T-SQL位可以在这里找到：http：//www.devx.com/tips/Tip/28529

#2

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

我知道你已经接受了答案，但为什么不使用更简单的sp_tables呢？

sp_tables 'Database_Name'

#3

Try this

尝试这个

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

#4

Try this :

尝试这个 ：

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

also look for other information_schema views.

还要查找其他information_schema视图。

#5

Try this:

尝试这个：

USE your_database
go
Sp_tables
go

#6

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

而且，由于INFORMATION_SCHEMA是SQL-92标准的一部分，许多数据库都支持它 - 包括MySQL。

#7

Try following

试试以下

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

#8

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

MS正在逐步淘汰information_schema视图以外的方法。所以为了向前兼容，总是使用那些。

#9

Try it :

尝试一下 ：

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

#10

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.

通常我会说访问information_schema.tables视图，但是在发现PDO无法从不同的数据对象访问该数据库时，我需要找到一种不同的方式。在使用非特权用户或PDO时，看起来像sp_tables'Database_Name是更好的方法。