Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/*****************************************************************************
Copyright (c) 2018 SQL Workbooks LLC
MIT License, http://www.opensource.org/licenses/mit-license.php
Contact: help@sqlworkbooks.com
*****************************************************************************/
USE master;
GO
CREATE OR ALTER PROCEDURE dbo.sp_indexdetails (
@databasename sysname = null,
@tablename sysname,
@debug tinyint = 0
)
AS
IF @databasename is null
SET @databasename= DB_NAME()
ELSE
IF (SELECT COUNT(*) from sys.databases where name=@databasename) = 0
BEGIN
PRINT 'Database not found.'
RETURN
END
DECLARE @dsql NVARCHAR(MAX);
SET @dsql = N'
SELECT
DB_NAME() AS database_name,
sc.name + N''.'' + t.name AS table_name,
(SELECT MAX(user_reads)
FROM (VALUES (last_user_seek), (last_user_scan), (last_user_lookup)) AS value(user_reads)) AS last_user_read,
last_user_update,
CASE si.index_id WHEN 0 THEN N''/* No create statement (Heap) */''
ELSE
CASE is_primary_key WHEN 1 THEN
N''ALTER TABLE '' + QUOTENAME(sc.name) + N''.'' + QUOTENAME(t.name) + N'' ADD CONSTRAINT '' + QUOTENAME(si.name) + N'' PRIMARY KEY '' +
CASE WHEN si.index_id > 1 THEN N''NON'' ELSE N'''' END + N''CLUSTERED ''
ELSE N''CREATE '' +
CASE WHEN si.is_unique = 1 then N''UNIQUE '' ELSE N'''' END +
CASE WHEN si.index_id > 1 THEN N''NON'' ELSE N'''' END + N''CLUSTERED '' +
N''INDEX '' + QUOTENAME(si.name) + N'' ON '' + QUOTENAME(sc.name) + N''.'' + QUOTENAME(t.name) + N'' ''
END +
/* key def */ N''('' + key_definition + N'')'' +
/* includes */ CASE WHEN include_definition IS NOT NULL THEN
N'' INCLUDE ('' + include_definition + N'')''
ELSE N''''
END +
/* filters */ CASE WHEN filter_definition IS NOT NULL THEN
N'' WHERE '' + filter_definition ELSE N''''
END +
/* with clause - compression goes here */
CASE WHEN row_compression_partition_list IS NOT NULL OR page_compression_partition_list IS NOT NULL
THEN N'' WITH ('' +
CASE WHEN row_compression_partition_list IS NOT NULL THEN
N''DATA_COMPRESSION = ROW '' + CASE WHEN psc.name IS NULL THEN N'''' ELSE + N'' ON PARTITIONS ('' + row_compression_partition_list + N'')'' END
ELSE N'''' END +
CASE WHEN row_compression_partition_list IS NOT NULL AND page_compression_partition_list IS NOT NULL THEN N'', '' ELSE N'''' END +
CASE WHEN page_compression_partition_list IS NOT NULL THEN
N''DATA_COMPRESSION = PAGE '' + CASE WHEN psc.name IS NULL THEN N'''' ELSE + N'' ON PARTITIONS ('' + page_compression_partition_list + N'')'' END
ELSE N'''' END
+ N'')''
ELSE N''''
END +
/* ON where? filegroup? partition scheme? */
'' ON '' + CASE WHEN psc.name is null
THEN ISNULL(QUOTENAME(fg.name),N'''')
ELSE psc.name + N'' ('' + partitioning_column.column_name + N'')''
END
+ N'';''
END AS index_create_statement,
si.index_id,
si.name AS index_name,
partition_sums.in_row_data_page_count,
partition_sums.reserved_in_row_GB,
partition_sums.reserved_LOB_GB,
partition_sums.row_count,
stat.user_seeks,
stat.user_scans,
stat.user_lookups,
user_updates AS queries_that_modified,
partition_sums.partition_count,
si.allow_page_locks,
si.allow_row_locks,
si.is_hypothetical,
si.has_filter,
si.fill_factor,
si.is_unique,
ISNULL(pf.name, ''/* Not partitioned */'') AS partition_function,
ISNULL(psc.name, fg.name) AS partition_scheme_or_filegroup,
t.create_date AS table_created_date,
t.modify_date AS table_modify_date';
SET @dsql = @dsql + N'
FROM ' + quotename(@databasename) + N'.sys.indexes AS si
JOIN ' + quotename(@databasename) + N'.sys.tables AS t ON si.object_id=t.object_id
JOIN ' + quotename(@databasename) + N'.sys.schemas AS sc ON t.schema_id=sc.schema_id
LEFT JOIN ' + quotename(@databasename) + N'.sys.dm_db_index_usage_stats AS stat ON
stat.database_id = DB_ID()
and si.object_id=stat.object_id
and si.index_id=stat.index_id
LEFT JOIN ' + quotename(@databasename) + N'.sys.partition_schemes AS psc ON si.data_space_id=psc.data_space_id
LEFT JOIN ' + quotename(@databasename) + N'.sys.partition_functions AS pf ON psc.function_id=pf.function_id
LEFT JOIN ' + quotename(@databasename) + N'.sys.filegroups AS fg ON si.data_space_id=fg.data_space_id
/* Key list */ OUTER APPLY ( SELECT STUFF (
(SELECT N'', '' + QUOTENAME(c.name) +
CASE ic.is_descending_key WHEN 1 then N'' DESC'' ELSE N'''' END
FROM ' + quotename(@databasename) + N'.sys.index_columns AS ic
JOIN ' + quotename(@databasename) + N'.sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.key_ordinal > 0
ORDER BY ic.key_ordinal FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''),1,2,'''')) AS keys ( key_definition )
/* Partitioning Ordinal */ OUTER APPLY (
SELECT MAX(QUOTENAME(c.name)) AS column_name
FROM ' + quotename(@databasename) + N'.sys.index_columns AS ic
JOIN ' + quotename(@databasename) + N'.sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.partition_ordinal = 1) AS partitioning_column
/* Include list */ OUTER APPLY ( SELECT STUFF (
(SELECT N'', '' + QUOTENAME(c.name)
FROM ' + quotename(@databasename) + N'.sys.index_columns AS ic
JOIN ' + quotename(@databasename) + N'.sys.columns AS c ON
ic.column_id=c.column_id
and ic.object_id=c.object_id
WHERE ic.object_id = si.object_id
and ic.index_id=si.index_id
and ic.is_included_column = 1
ORDER BY c.name FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''),1,2,'''')) AS includes ( include_definition )
/* Partitions */ OUTER APPLY (
SELECT
COUNT(*) AS partition_count,
SUM(ps.in_row_data_page_count) AS in_row_data_page_count,
CAST(SUM(ps.in_row_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_in_row_GB,
CAST(SUM(ps.lob_reserved_page_count)*8./1024./1024. AS NUMERIC(32,1)) AS reserved_LOB_GB,
SUM(ps.row_count) AS row_count
FROM ' + quotename(@databasename) + N'.sys.partitions AS p
JOIN ' + quotename(@databasename) + N'.sys.dm_db_partition_stats AS ps ON
p.partition_id=ps.partition_id
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
) AS partition_sums
/* row compression list by partition */ OUTER APPLY ( SELECT STUFF (
(SELECT N'', '' + CAST(p.partition_number AS VARCHAR(32))
FROM ' + quotename(@databasename) + N'.sys.partitions AS p
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
and p.data_compression = 1
ORDER BY p.partition_number FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''),1,2,'''')) AS row_compression_clause ( row_compression_partition_list )
/* data compression list by partition */ OUTER APPLY ( SELECT STUFF (
(SELECT N'', '' + CAST(p.partition_number AS VARCHAR(32))
FROM ' + quotename(@databasename) + N'.sys.partitions AS p
WHERE p.object_id = si.object_id
and p.index_id=si.index_id
and p.data_compression = 2
ORDER BY p.partition_number FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''),1,2,'''')) AS page_compression_clause ( page_compression_partition_list )
WHERE
si.type IN (0,1,2) /* heap, clustered, nonclustered */
and t.name=@tablename
'
IF @debug = 1
PRINT @dsql;
EXEC sp_executesql @stmt = @dsql, @params = N'@tablename sysname', @tablename=@tablename;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.