Monday, September 9, 2013

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'
GO
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.

No comments:

Tutorials on SAP-ABAP

Adobe Interactive Forms Tutorials

Business Server Pages (BSP)

Userexits/BADIs

Web Dynpro for ABAP (Step by step procedure for web dynpro,Tutorials on Web Dynpro,)

ALV Tutorials

Blog Archive

goodsites