How To Get a List of Columns in a View using "sys.columns" in MS SQL Server?
Submitted by: AdministratorIf 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 = 'ggl_links_top'
GO
<pre>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</pre>
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.
Submitted by: Administrator
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 = 'ggl_links_top'
GO
<pre>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</pre>
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.
Submitted by: Administrator
Read Online MS SQL Server Job Interview Questions And Answers
Top MS SQL Server Questions
☺ | What Happens If NULL Values Are Involved in Bitwise Operations? |
☺ | PHP MSSQL - How To Display a Past Time in Days, Hours and Minutes? |
☺ | PHP MSSQL - How To Delete Existing Rows in a Table? |
☺ | What Is a Subquery in a SELECT Query Statement in MS SQL Server? |
☺ | How To Use Subqueries with the IN Operators in MS SQL Server? |
Top Databases Programming Categories
☺ | RDBMS Interview Questions. |
☺ | SQL Interview Questions. |
☺ | SSRS Interview Questions. |
☺ | Sybase Interview Questions. |
☺ | Database Administrator (DBA) Interview Questions. |