Nothing exciting, just a general script. How can you see what heaps you have laying around?
exec sp_MSforeachdb @command1 ='
use [?]
SELECT object_Name(i.object_id)
AS TableName, i.object_id,
Schema_Name(o.schema_id)
AS SchemaName, i.name, avg_fragmentation_in_percent, page_count
FROM sys.dm_db_index_physical_stats (NULL, NULL, NULL, NULL, NULL) p
inner join sys.objects as o on p.object_id = o.object_id
inner join sys.schemas as s on o.schema_id = s.schema_id
inner join sys.indexes as i on p.object_id = i.object_id
and p.index_id = i.index_id
where i.index_id = 0
and page_count > 1000
'
Adjust the page count to what ever works for your area. This is just for exploratory uses.
Great script. I made a couple of edits to help with readability. Then, when I ran it, I could not tell which database the results came from so I added a line like this:
ReplyDeleteSELECT ''?'' AS [Database], COUNT(1) AS [Tables] FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE''
Now when run with results to text I can see which database is responding. The part that shows the count of tables is just my way of knowing what the percentage of goo is in the structure.
I have an updated and faster version as well. Below will run a lot faster and pull roughly the same data depending if that's all you're looking for.
Deleteselect distinct db.name as DatabaseName, sch.name as SchemaName, idx.name as indexname, tbl.name as tablename,
part.used_page_count, part.reserved_page_count, part.row_count, fill_factor,
max_column_id_used, idx.type_desc
from sys.dm_db_index_usage_stats usage
inner join sys.dm_db_partition_stats as part on usage.object_id = part.object_id
inner join sys.tables as tbl on usage.object_id = tbl.object_id
inner join sys.indexes as idx on part.object_id = idx.object_id
and part.index_id = idx.index_id
inner join sys.schemas as sch on tbl.schema_id = sch.schema_id
inner join sys.databases as db on usage.database_id = db.database_id
where idx.index_id = 0