RowCount and Column Count For Each Table

Today, I got task to come up with the number of rows and number of columns for each table in given database.

This information is useful in defining the complexity while migrating database.

Below query is used to get such information.

Query:

SELECT A.NAME,COUNT(B.NAME) AS [NO.OFCOLUMNS],PT.ROW_COUNT --INTO #TEMP_COL

FROM SYS.TABLES A

JOIN SYS.COLUMNS B

ON A.OBJECT_ID=B.OBJECT_ID

JOIN SYS.DM_DB_PARTITION_STATS PT

ON A.OBJECT_ID=PT.OBJECT_ID

WHERE

PT.INDEX_ID < 2

GROUP BY A.NAME,PT.ROW_COUNT

Result: