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.

Twotime

Answer 1

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.

Demantoid

well, INFORMATION_SCHEMA is slow when you have lots of tables (query like this take about 30s on my database where i have ~1800 tables - more then half of them created by bug, thats why we noticed it - query to sys.columns is almost instant). Yes, i know, this is not realy the "generally" situation, just FYI :) – Macey

If you work with synonyms, you should not take INFORMATION_SCHEMA, at least if you believe How to find all column names of a synonym. – Mere

Answer 2

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

Trembly

Answer 3

Also: I was writing dymamic sql to insert back into table and you can't insert into calculated columns and the INFORMATION_SCHEMA doesn't seem to have that, where as you can use sys.columns to exclude columns in the INSERT statement that are calculated.

Lafleur

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center. – Giefer