To list all databases in a SQL Server instance, run the following:

SELECT *
FROM sys.sysdatabases

There are a few methods for listing all the tables in a database. Some methods will be able to show more specifics than others due to the capabilities inherent in the tables being joined. I’ll show you the simplest way first which is probably the most supported way.

SELECT TABLE_SCHEMA + '.' + TABLE_NAME, *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
ORDER BY TABLE_SCHEMA + '.' + TABLE_NAME

This method will show you all the tables, and it will also display the rowcount and datasize in Megabytes.

CREATE TABLE #temp
  (
     table_name    SYSNAME,
     row_count     INT,
     reserved_size VARCHAR(50),
     data_size     VARCHAR(50),
     index_size    VARCHAR(50),
     unused_size   VARCHAR(50)
  )

SET NOCOUNT ON

INSERT #temp
EXEC Sp_msforeachtable 'sp_spaceused ''?'''

SELECT a.table_name,
       a.row_count,
       COUNT(*) AS col_count,
       a.data_size
FROM   #temp a
       INNER JOIN information_schema.columns b
         ON a.table_name COLLATE database_default = b.table_name COLLATE database_default
GROUP  BY a.table_name,
          a.row_count,
          a.data_size
ORDER  BY CAST(REPLACE(a.data_size, ' KB', '') AS INTEGER) DESC

DROP TABLE #temp