Su SQL Server sono disponibili delle viste di sistema per questo.
Ti consiglio di ricercare INFORMATION_SCHEMA sui Books On Line di SQL Server.

Es SELECT * FROM INFORMATION_SCHEMA.TABLES
restituisce tutte le tabelle del db