CODEWITHSUNDEEP
sql-serverdatabasedatabase-designmetadata
todorojo
todorojo

Reputation: 263

How does the column system table (sys.columns, sys.all_columns, etc.) describe itself?

I noticed in SQL Server 2008 that the sys.all_columns table includes its own columns. Hows is that possible? It seems like a chicken/egg problem -- how can SQL Server determine the columns of sys.all_columns without first reading sys.all_columns, which requires knowing what the columns of that table are?

Also, is this common? Do other database systems do this?

Upvotes: 4

Views: 3865

Answers (2)

Jason Whitish
Jason Whitish

Reputation: 1438

sys.all_columns and sys.columns aren't tables, they're views. In fact, sys.all_columns is a combination of sys.system_columns and sys.columns.

They all tie back into a base table that I'm pretty sure Microsoft prefers people don't mess around with.

You can learn more about the system base tables here: https://msdn.microsoft.com/en-us/library/ms179503.aspx

Upvotes: 3

KthProg
KthProg

Reputation: 2117

It could run the initial query, then query itself for the new columns and take the union. Really though, the initial query itself describes the columns before the query ever runs. That's why you can't define a view with unnamed columns.

Upvotes: 0

Related Questions