How can I see if the data in a SQL Server table is page-compressed?

To see whether or not data pages are indeed currently "PAGE" compressed, you can use the undocumented DMF sys.dm_db_database_page_allocations(). The is_page_compressed field contains the info you are looking for. You will need to use the DETAILED mode (i.e. 5th parameter) or else the values in that field will all be NULL.

To be clear (based on the wording of the question being, "what can I do to find out if the data in that table is page-compressed?"), this is not an all-or-none issue: page compression is applied per each data page, hence you can have none that are compressed, all being compressed, or any combination in between. So, you need to look at all of the pages. And no, you cannot necessarily assume that a single non-page compressed page indicates that you need to REBUILD because a non-filled page will not compress.

For example:

SELECT [is_page_compressed]
FROM   sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.CompressedHeap'),
                                           0, 1, 'DETAILED');

The following shows that the data pages are not initially page compressed, but they are after the REBUILD operation:

USE [tempdb];

-- DROP TABLE dbo.CompressedHeap;
CREATE TABLE dbo.CompressedHeap
(
  ID INT IDENTITY(1, 1) NOT NULL,
  String sysname,
  [MaxLength] SMALLINT,
  [Type] VARCHAR(5)
) WITH (DATA_COMPRESSION = PAGE);


INSERT INTO dbo.CompressedHeap ([String], [MaxLength], [Type])
  SELECT col.[name], col.[max_length], obj.[type]
  FROM   master.sys.columns col
  CROSS JOIN master.sys.objects obj;


SELECT [is_page_compressed], *
FROM   sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.CompressedHeap'),
                                           0, 1, 'DETAILED')
WHERE  [is_iam_page] = 0
AND    [is_allocated] = 1;
-- 394 pages


ALTER TABLE dbo.CompressedHeap REBUILD;


SELECT [is_page_compressed], *
FROM   sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID(N'dbo.CompressedHeap'),
                                           0, 1, 'DETAILED')
WHERE  [is_iam_page] = 0
AND    [is_allocated] = 1;
-- 179 pages