December 28, 2012

Get the columns used by a table on Microsoft SQL.

On Microsoft SQL the columns are stored on the sys.columns table, and related to the object ID of the tables stored on sys.tables. I had to obtain all the columns of some particular tables so I selected the table names and its object ID from the sys.tables table and croos applied the columns obtained from the sys.columns table that had as object ID the IDs of the tables that I want.

Select p.name, p.object_id, b.name
from sys.tables p
Cross apply (Select * from sys.columns) b
where b.object_id = p.object_id and (p.object_id = OBJECT_ID('tst.USR02') or p.object_id = OBJECT_ID('tst.USR01'))