Like we had query to find no of columns in a Table, is there any similar query to find total no of columns in a Sql Server View ?
4 Answers
Even simpler is to use sys.columns.
select count(*)
from sys.columns
where OBJECT_ID = OBJECT_ID('YourView')
1 Comment
Gottfried Lesigang
This is simpler indeed. The advantage of the other approaches is: They would work with any ad-hoc statement, joined sources etc. But the OP wasn't asking for this actually. So +1 from my side ;-)
Similar to Larnu's comment, I tend to prefer the Table-Valued-Function
Example
Declare @tsql nvarchar(max) = N'Select * from YourView_Table_Or_Query'
Select column_ordinal
,name
,system_type_name
From sys.dm_exec_describe_first_result_set(@tsql,null,null )
-- Or for the Count
Select ColumnCnt=count(*)
From sys.dm_exec_describe_first_result_set(@tsql,null,null)
2 Comments
Gottfried Lesigang
Ah, yes! forgot about the TVF (introduced with v2012). +1 From my side
Thom A
As did I. I should really begin using that; far easier than shoving everything in a temporary table.
Besides the rather clumsy procedure sp_describe_first_reuslt_set you can use the generic abilities of XML:
SELECT (SELECT TOP 1 * FROM YourView FOR XML RAW, ELEMENTS XSINIL ,TYPE).value('count(/row/*)','int');
Edit: Forgot to add ELEMENTS XSNIL which would omit columns with a value of NULL otherwise...
answered Feb 28, 2019 at 16:38
Gottfried Lesigang
67.6k9 gold badges60 silver badges124 bronze badges
Comments
--this might help
SELECT v.name, count(1) ColumnCount
FROM SYS.VIEWS v
INNER JOIN SYS.all_columns c ON v.object_id = c.object_id
WHERE v.name IN (SELECT name FROM sys.VIEWS)
GROUP BY v.name
ORDER BY v.name
SELECT name FROM sys.VIEWS, can be replaced by viewname/s or query which returns viewname/s
1 Comment
Community
As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.