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

sp_tables 'Database_Name'

USE your_database
go
Sp_tables
go

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

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

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

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

SELECT * FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA='dbo';