Query for All Tables and Row Counts in a SQL Server Database

-- I end up using this one a lot. You can add it into a JOIN to filter out tables that are empty.

SELECT O.object_id
       , SCHEMA_NAME(O.schema_id) [Schema]
       ,  O.name [Table]
       , SUM(P.Rows) [RowCount] 
       , O.modify_date
        , O.create_date
FROM sys.objects O
JOIN sys.partitions P
       ON O.object_id = P.object_id
WHERE O.type = 'U'
      AND O.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY O.object_id, O.name, O.create_date, SCHEMA_NAME(O.schema_id)
  , O.modify_date
HAVING SUM(P.Rows) > 0

-- For example:
DECLARE @VAR VARCHAR(100)
SET @VAR = 'Descp' -- replace the value between the single quotes with your search param

SELECT T.name [TABLE], C.name [Column], O.[RowCount] 
FROM sys.tables T
JOIN sys.columns C
         ON C.object_id = T.object_id
JOIN (
       SELECT O.object_id
         , O.name [Table]
      , SUM(P.Rows) [RowCount] 
         , O.modify_date
         , O.create_date
       FROM sys.objects O
       JOIN sys.partitions P
              ON O.object_id = P.object_id
       WHERE O.type = 'U'
                AND O.is_ms_shipped = 0x0
                AND index_id < 2
       GROUP BY O.object_id, O.name, O.create_date, SCHEMA_NAME(O.schema_id), O.modify_date
       HAVING SUM(P.Rows) > 0) O
  ON O.object_id = T.object_id

WHERE C.name LIKE '%' + @VAR + '%'