1

I am trying to run Kendra's script for all DBs but running into issues.I have tried below.

http://www.littlekendra.com/2016/05/05/how-to-script-out-indexes-from-sql-server/

DECLARE @SQLCmd VARCHAR(MAX); 
DECLARE @LoopCounter INT; 
DECLARE @WorkingDB sysname; 

IF OBJECT_ID('tempdb..##DBIndexDetails') IS NOT NULL 
DROP TABLE ##DBIndexDetails; 

CREATE TABLE ##DBIndexDetails 
( 
database_name   nvarchar(256), 
table_name  nvarchar(256), 
index_create_statement  nvarchar(4000), 
index_id    int , 
index_name  sysname , 
partition_count int , 
allow_page_locks    bit , 
allow_row_locks bit , 
is_hypothetical bit , 
has_filter  bit , 
fill_factor tinyint , 
is_unique   bit , 
partition_function  sysname , 
partition_scheme_or_filegroup   sysname , 
table_created_date  datetime    , 
table_modify_date   datetime    
); 


DECLARE @DatabasesTable TABLE ( DBName sysname ); 

INSERT INTO @DatabasesTable 
SELECT name 
FROM sys.databases 
WHERE source_database_id IS NULL /* no database snapshots */ 
AND is_read_only = 0 /* no read-only dbs (log shipping) */ 
AND state = 0 /* online databases */ 
AND name NOT IN ( 'master', 'model', 'msdb', 'tempdb' ) /* non-system databases only */ 
ORDER BY name; 

SELECT @LoopCounter = COUNT(*) 
FROM @DatabasesTable; 

WHILE @LoopCounter > 0 
BEGIN 

SELECT TOP 1 
@WorkingDB = DBName 
FROM @DatabasesTable; 

SET @SQLCmd = ' 
USE [' + @WorkingDB + ']; 
SELECT 
DB_NAME() AS database_name, 
sc.name + N''.'' + t.name AS table_name, 

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.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 
FROM sys.indexes AS si 
JOIN sys.tables AS t ON si.object_id=t.object_id 
JOIN sys.schemas AS sc ON t.schema_id=sc.schema_id 
LEFT JOIN 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 sys.partition_schemes AS psc ON si.data_space_id=psc.data_space_id 
LEFT JOIN sys.partition_functions AS pf ON psc.function_id=pf.function_id 
LEFT JOIN 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 sys.index_columns AS ic 
JOIN 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 sys.index_columns AS ic 
JOIN 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 sys.index_columns AS ic 
JOIN 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, 
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 sys.partitions AS p 
JOIN 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 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 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 */ 
ORDER BY table_name, si.index_id 
OPTION (RECOMPILE); 
GO'; 
PRINT (@SQLCmd); 
EXEC(@SQLCmd); 

DELETE FROM @DatabasesTable 
WHERE DBName = @WorkingDB; 

SET @LoopCounter -= 1; 

END; 


SELECT * 
FROM ##DBIndexDetails; 


DROP TABLE ##DBIndexDetails;  

Getting below error:

Msg 105, Level 15, State 1, Line 85 Unclosed quotation mark after the character string 'NVARC'. Msg 102, Level 15, State 1, Line 85 Incorrect syntax near 'NVARC'.

When I print the command I see the whole command is not coming out of @SQLCmd variable which is causing the issue. I am unable to figure that out. Could be missing or extra quotes issue but I am not sure though. Please help me fix this issue.

  • If I remember correctly, printwill only show the first 4000 characters, so you might want to add more prints using substring – James Z Aug 20 '16 at 7:54
  • 1
    Or try SELECT @SqlCmd AS [processing-instruction(x)] FOR XML PATH('') instead of print – Martin Smith Aug 20 '16 at 9:17
  • Replace print with select and you should see the while thing. – Greg Aug 21 '16 at 3:29
  • 2
    It works for me. I had to exclude GO from the end. Which version of SQL you are running? – Slava Murygin Aug 22 '16 at 1:09
  • @SlavaMurygin: That was the issue. Removing GO works. Thanks! – SQLPRODDBA Aug 22 '16 at 9:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.