more number of rows in database

Vineet S 1,070

HI,

How to get view which has more number of rows in to it in sql server database

Accepted answer

LiHongMSFT-4306 28,121

Hi @Vineet S

Try this:

DECLARE @sql VARCHAR(MAX)
DECLARE @view_elements VARCHAR(MAX)

SELECT @view_elements = ISNULL( @view_elements ,'') + Part
FROM (SELECT CONCAT(' UNION ALL ','select ','''',s.name,'.',v.name,'''',',', 'count(*) from ',s.name,'.',v.name) AS Part
      FROM sys.views v INNER JOIN sys.schemas s ON s.schema_id = v.schema_id) S
GROUP BY Part
--PRINT @view_elements

SET @sql = ';WITH CTE AS ( SELECT '''' AS ViewName, 0 AS CountRows'+ @view_elements +') SELECT TOP(3) * FROM CTE WHERE CountRows > 40 ORDER BY CountRows DESC'
--PRINT (@sql)

EXEC (@sql)

Best regards,

Cosmog

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

2 additional answers

Erland Sommarskog 112.7K MVP

CREATE TABLE #views(viewname nvarchar(256) PRIMARY KEY, cnt int NOT NULL)
DECLARE @sql nvarchar(MAX)
SELECT @sql = string_agg(convert(nvarchar(MAX), 'INSERT #views(viewname, cnt) SELECT ' + 
                         quotename(s.name + '.' + v.name, '''') + ', COUNT(*) 
                         FROM ' + quotename(s.name) + '.' + quotename(v.name)), char(13) + char(10))
FROM   sys.views v
JOIN   sys.schemas s ON v.schema_id = s.schema_id

PRINT @sql
EXEC (@sql)

SELECT TOP 3 viewname, cnt
FROM   #views
ORDER  BY cnt DESC
go
DROP TABLE #views

Zahid Butt 956 Reputation points

2024-07-31T12:10:04.83+00:00

Hi,

Please use below query in SSMS & scroll down to view all rows in your table:

select * from TableName

If otherwise pls describe your requirement in detail.

Regards,
Please sign in to rate this answer.
Vineet S 1,070 Reputation points

2024-07-31T12:15:46.4533333+00:00

I want to get list of SQL server views for which has large number of rows or data

Olaf Helper 45,111 Reputation points

2024-07-31T13:02:12.6366667+00:00

Larger number of which "rows"?

Your post is more then unclear, please describe your requirement more detailed, may with some samples.

Vineet S 1,070 Reputation points

2024-07-31T13:17:56.6233333+00:00

Example view1 contains 30 rows ,view2 contains 40 rows or count of records SO I want top 3 views which has 40 records in it or 40 rows in it

Zahid Butt 956 Reputation points

2024-07-31T13:29:08.99+00:00

Pls use below query:

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'UNION ALL

SELECT '

+ 'N''' + QUOTENAME(s.name) + N'.' + QUOTENAME(v.name) + N''', FromMetadata = ' + CONVERT(varchar(11),COALESCE(SUM(p.rows),0)) + N', TheHarderWay = COUNT(*) FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(v.name) + N'

'

FROM sys.views AS v

INNER JOIN sys.schemas AS s

ON v.schema_id = s.schema_id

LEFT OUTER JOIN sys.partitions AS p

ON v.object_id = p.object_id

AND p.index_id IN (0,1)

GROUP BY s.name, v.name

ORDER BY s.name, v.name;

SET @sql = STUFF(@sql, 1, CHARINDEX(N'SELECT', @sql)-1, N'');

--PRINT @sql;

EXEC sys.sp_executesql @sql;

You may find it on: https://dba.stackexchange.com/questions/237999/sql-script-to-count-rows-for-all-views-in-a-database-with-select-only-access

Zahid Butt 956 Reputation points

2024-07-31T13:32:13.18+00:00

Sorry I could not paste query properly, pls copy the whole code.

Vineet S 1,070 Reputation points

2024-07-31T15:25:07.6133333+00:00

what does this view says

Vineet S 1,070 Reputation points

2024-07-31T15:27:09.26+00:00

Warning: Null value is eliminated by an aggregate or other SET operation.

Msg 8623, Level 16, State 1, Line 1

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Completion time: 2024-07-31T20:55:27.5475888+05:30 not working

Zahid Butt 956 Reputation points

2024-07-31T15:30:45.6166667+00:00

When you execute above query within a database, it will give you three column output.

First column is view name & other two are row counts(FromMetadata & TheHarderWay) .In my case third column is giving perfect count (TheHarderWay).
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

more number of rows in database

2 additional answers

Your answer