John Kamau johnkamau
johnkamau
/ Script-out-all-indexes-in-a-database.sql
Last active
August 23, 2022 14:39
— forked from LitKnd/Script-out-all-indexes-in-a-database.sql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''' + QUOTENAME(sc.name) + N'.' + QUOTENAME(t.name) + ''') AND name = N'''+ si.name +''')' + CHAR(10) + '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 ' |