Sql Server tips and tricks
This is part of a series of quick tips and tricks I have accumulated over the year, that I think can be useful for others.
If you have similar short tips and tricks please leave a comment.
Number of rows in all tables
When you start to work on a project for a new customer a good piece of information is how many tables does the database contain and how many rows are there in each table. Or maybe you don't have access to the production database and need to send a service request to the IT operations department to get information about the production database.
Then the query below will useful to you.
SELECT SCHEMA_NAME(sOBJ.schema_id) + '.' + sOBJ.name AS [Table name],
SUM(sPTN.Rows) AS [Row count]
FROM sys.objects AS sOBJ
JOIN sys.partitions AS sPTN ON sOBJ.object_id = sPTN.object_id
WHERE sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY sOBJ.schema_id, sOBJ.name
ORDER BY [Table name]
Top comments (0)