SELECT Schema_name(tbl.schema_id) AS [Schema],
tbl.name,
Coalesce((SELECT pr.name
FROM sys.database_principals pr WITH (nolock)
WHERE pr.principal_id = tbl.principal_id),
Schema_name(tbl.schema_id)) AS [Owner],
tbl.max_column_id_used AS [Columns],
CAST(CASE idx.index_id
WHEN 1 THEN 1
ELSE 0
END AS BIT) AS
[HasClusIdx],
Coalesce((SELECT SUM (spart.ROWS)
FROM sys.partitions spart WITH (nolock)
WHERE spart.object_id = tbl.object_id
AND spart.index_id < 2), 0) AS [RowCount],
Coalesce((SELECT CAST(v.low / 1024.0 AS FLOAT) * SUM(a.used_pages - CASE
WHEN a.TYPE <> 1 THEN a.used_pages
WHEN p.index_id < 2 THEN a.data_pages
ELSE 0
END)
FROM sys.indexes AS i WITH (nolock)
JOIN sys.partitions AS p WITH (nolock)
ON p.object_id = i.object_id
AND p.index_id = i.index_id
JOIN sys.allocation_units AS a WITH (nolock)
ON a.container_id = p.partition_id
WHERE i.object_id = tbl.object_id), 0.0) / 1024 AS [IndexMB],
Coalesce((SELECT CAST(v.low / 1024.0 AS FLOAT) * SUM(CASE
WHEN a.TYPE <> 1 THEN a.used_pages
WHEN p.index_id < 2 THEN a.data_pages
ELSE 0
END)
FROM sys.indexes AS i WITH (nolock)
JOIN sys.partitions AS p WITH (nolock)
ON p.object_id = i.object_id
AND p.index_id = i.index_id
JOIN sys.allocation_units AS a WITH (nolock)
ON a.container_id = p.partition_id
WHERE i.object_id = tbl.object_id), 0.0) / 1024 AS [DataMB],
tbl.create_date,
tbl.modify_date
FROM sys.tables AS tbl WITH (nolock)
INNER JOIN sys.indexes AS idx WITH (nolock)
ON ( idx.object_id = tbl.object_id
AND idx.index_id < 2 )
INNER JOIN MASTER.dbo.spt_values v WITH (nolock)
ON ( v.NUMBER = 1
AND v.TYPE = 'E' )
--WHERE tbl.Name like '%tablename%'
ORDER BY 8 DESC