-- 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 + '%'
