Home >> FAQs/Tutorials >> SQL Server FAQ
SQL Server FAQ - "sys.columns" - Getting a List of Columns in a View
(Continued from previous topic...)
How To Get a List of Columns in a View using "sys.columns"?
If you have an existing view, but you don't remember what are the columns defined
in the view, you can use the "sys.columns" system view to get a list
of all columns of all views in the current database.
In order to a list of columns of a single view, you need to join sys.columns and
sys.views as shown in the tutorial example below:
SELECT * FROM sys.columns c, sys.views v
WHERE c.object_id = v.object_id
AND t.name = 'fyi_links_top'
object_id name column_id user_type_id max_length
----------- ------- ---------- ------------ ----------
1205579333 id 1 56 4
1205579333 counts 2 56 4
1205579333 url 3 167 80
You can see the column names easily from the sys.columns view. But you can only see
the column type IDs. This requires another join to get the column type names. You may try
the "sp_columns" stored procedure to get a better list of columns shown in the next tutorial.
(Continued on next topic...)
- What Are Views?
- How To Create a View on an Existing Table?
- How To See Existing Views?
- How To Drop Existing Views from a Database?
- How To Get a List of Columns in a View using "sys.columns"?
- How To Get a List of Columns in a View using the "sp_columns" Stored Procedure?
- How To Get a List of Columns in a View using the "sp_help" Stored Procedure?
- How To Generate CREATE VIEW Script on an Existing View?
- How To Get the Definition of a View Out of the SQL Server?
- Can You Create a View with Data from Multiple Tables?
- Can You Create a View using Data from Another View?
- What Happens If You Delete a Table That Is Used by a View?
- Can You Use ORDER BY When Defining a View?
- How To Modify the Underlying Query of an Existing View?
- Can You Insert Data into a View?
- Can You Update Data in a View?
- Can You Delete Data from a View?
- How To Assign New Column Names in a View?
- How Column Data Types Are Determined in a View?
- How To Bind a View to the Schema of the Underlying Tables?
- How To Create an Index on a View?