Joining sys.columns and sys.tables on database name

Question

I'm writing a application using a database backend, but I need to find out all the column names and the table they belong to via SQL. I know you can query sys.columns, sys.tables, and sys.databases, but what I want to do is join them all to get something like the below result (basically displaying all columns and tables from the DB):

    +-----------+------------+
    | Tablename | columnname |
    +-----------+------------+
    | Table1    | Id         |
    | Table1    | Name       |
    | Table1    | Owner      |
    | Table2    | Id         |
    | Table2    | Name       |
    | Table2    | True       |
    +-----------+------------+

I've had a good look in the sys schema, but I can't find any parent reference in sys.columns or sys.tables.

Answer 1

Randy Minder's answer is the most correct one. But in case if you want to continue with sys.columns and sys.tables join them on object_id.

select   tab.name, col.name
from    sys.columns col
inner join sys.tables tab
    on col.object_id = tab.object_id

You can use this to get the table names from sys.columns table it self.

select object_name(object_id),name 
from sys.columns

Answer 2

You generally do not want to query the sys.columns or sys.tables (or any system tables) directly. You should be using the INFORMATION_SCHEMA views. These views are the ANSI standard way of querying system tables that could change from release to release. The INFORMATION_SCHEMA views will not change, at least in a breaking way.

SELECT COLUMN_NAME,* 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = <TableName> AND TABLE_SCHEMA= <SchemaName>

Of course, the WHERE clause is optional here and could be omitted to see all columns in all tables etc.