One of the person was asking on forums about how to find the number of rows in each table in SQL. So I am writing this blog and showing the script will give you a list of tables with count of rows. Here is the script :
DECLARE @temp TABLE
([TableName] VARCHAR(255),[NumberOfRows] BIGINT)
INSERT INTO @temp
EXEC sp_MSForEachtable 'SELECT ''?'',COUNT(*) FROM ?'
SELECT * FROM @temp ORDER BY TableName
What I have done above is just created a temp table and I run the sp_MSforeachtable stored procedure with the paramater'SELECT ''?'',COUNT(*) FROM ?''. sp_MSforeachtable will loop through all the tables in the database. Below is the screenshot of the output which I was getting when I run the same query in AdventureWorks DB.
Do let me know your feedback, comments.