SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

How many heap tables do you have? :Quick Tips

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.

DBA On The Go

I've been working in Enterprise IT environments for roughly 10 years. My move to become a DBA was new, but expected. I've worked in secure DoD databases and secure Pharmaceutical databases. One thing to note, the security aspect isn't a lot different. My goal is to marry Databases and security in a functional manner and see how we can speed these things up with minimal work.

Comments

Leave a comment on the original post [www.dbaonthego.com, opens in a new window]

Loading comments...