Find all tables without foreign keys in a database

SwapnilBhate's answer will return tables that are referenced by a foreign key. Adding a further LEFT JOIN will exclude these:

SELECT tbl.name 
FROM sys.tables AS tbl
    LEFT JOIN sys.foreign_key_columns AS fKey 
        ON tbl.object_id = fKey.parent_object_id
    LEFT JOIN sys.foreign_key_columns AS rKey 
        ON tbl.object_id = rKey.referenced_object_id
WHERE fKey.parent_object_id IS NULL 
    AND rKey.referenced_object_id IS NULL;

You can make use of the sp_fkeys procedure to get the logical foreign key information. But the limitation is you have to execute is by passing the table name as the input parameter. More info for the same can be found at below MSDN library.

sp_fkeys

Alternatively you can make use of below query to get all the tables from sys.tables and check if the same table object_id exists in the sys.foreign_key_columns.

SELECT tbl.name
FROM sys.tables AS tbl
    LEFT JOIN sys.foreign_key_columns AS fKey
    ON tbl.object_id = fKey.parent_object_id
WHERE fKey.parent_object_id IS NULL

For the sake of completeness, you can also query sys.foreign_keys instead of sys.foreign_key_columns to find tables that are both unreferenced and not referencing others:

SELECT
  schema_name = s.name,
  table_name  = t.name
FROM
  sys.tables AS t
  INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id
  LEFT JOIN sys.foreign_keys AS fk
    ON t.object_id IN (fk.parent_object_id, fk.referenced_object_id)
WHERE
  t.is_ms_shipped = 0
  AND fk.object_id IS NULL
;

The query additionally shows each table' schema name, because sometimes systems are built using more than just the default dbo schema.

I have also included the is_ms_shipped = 0 filter in the query because I am assuming you want to exclude predefined/system tables from the search.