July 13, 2015 at 6:38 pm
a shortcut to a proc you might find useful, a quick way to show index details for a table, including rowcount, size etc:
USE [master]
go
create procedure [dbo].[sp_gettableindexinfo]
@objname nvarchar(776) = NULL
as
begin
set nocount on
SELECT s.name AS 'schema',
o.name AS 'table',
i.name AS indexname,
i.type_desc,
p.data_compression_desc,
fg.name as FileGroup,
replace(CONVERT(varchar, CAST(p.rows AS money), 1),'.00','') as rows,
i.is_unique,
i.is_primary_key,
i.is_unique_constraint,
i.is_disabled,
i.has_filter,
i.filter_definition,
CASE
WHEN ps.usedpages > ps.pages THEN ( ps.usedpages - ps.pages )
ELSE 0
END * 8 indexsizekb,
CASE
WHEN ps.usedpages > ps.pages THEN ( ps.usedpages - ps.pages )
ELSE 0
END * 8 / 1024 indexsizemb
FROM sys.indexes i (nolock)
left JOIN sys.data_spaces ds (nolock)
ON ds.data_space_id = i.data_space_id
LEFT OUTER JOIN sys.filegroups fg (nolock)
ON fg.data_space_id = ds.data_space_id
INNER JOIN sys.objects o (nolock)
ON o.object_id = i.object_id
LEFT JOIN sys.schemas s (nolock)
ON o.schema_id = s.schema_id
LEFT JOIN sys.partitions p (nolock)
ON i.index_id = p.index_id
AND i.object_id = p.object_id
INNER JOIN (SELECT object_id,
index_id,
SUM (used_page_count) usedpages,
SUM (CASE
WHEN ( index_id < 2 ) THEN (
in_row_data_page_count + lob_used_page_count
+
row_overflow_used_page_count )
ELSE lob_used_page_count +
row_overflow_used_page_count
END) pages
FROM sys.dm_db_partition_stats (nolock)
GROUP BY object_id,
index_id) ps
ON i.index_id = ps.index_id
AND i.object_id = ps.object_id
WHERE 1 = 1
AND o.type not in ( 's' , 'it')
and o.object_id =object_id(@objname)
order by CASE
WHEN ps.usedpages > ps.pages THEN ( ps.usedpages - ps.pages )
ELSE 0
END * 8 desc
;
end
create SSMS shortcut to sp_gettableindexinfo
July 14, 2015 at 12:26 am
Hi hdt
I will try to add it under ctrl+7 as i didn't have idea for it.
Thanks,
July 14, 2015 at 1:52 am
---
October 25, 2015 at 9:13 am
Latest version here:https://goo.gl/ggCkh7
October 27, 2015 at 2:36 am
This will be quite handy but I am getting error when I try any of the shortcuts for one of my existing tables:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '.'.
Any Idea ?
October 27, 2015 at 2:43 am
If you are including schema in the selection it has to be in quotes, also make sure at the end of shortcut you have comma.
October 27, 2015 at 2:53 am
Thanks ! Works with the quotes.
October 27, 2015 at 7:11 am
Thanks for the additional shortcuts.
October 28, 2015 at 2:09 pm
Does the SP need to executed on every server for these shortcuts to work?
October 28, 2015 at 3:18 pm
Yes it does
August 11, 2016 at 6:33 am
Got this last year from you. Still a great set of shortcuts.
August 11, 2016 at 6:53 am
Iwas Bornready (8/11/2016)
Got this last year from you. Still a great set of shortcuts.
Thank you Iwas,
Really appreciate the feedback 🙂 I've updated it recently fixing some minor things.
July 1, 2021 at 9:19 pm
This is an excellent set of scripts that I use daily!
I'm having problems with CTRL+8 finding a column name that contains an underscore which is a wildcard character. Is there a way to modify this so that I can search for column names containing an underscore?
Thanks,
Greg
Viewing 13 posts - 16 through 27 (of 27 total)
You must be logged in to reply to this topic. Login to reply