/SQL-Server-First-Responder-Kit

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

sp_BlitzIndex @Mode 2: add commands to drop, create index #1921

Closed
BrentOzar opened this Issue Jan 13, 2019 · 6 comments

Comments

Assignees
No one assigned
Labels
enhancement sp_BlitzIndex
Projects
None yet
Milestone
  2019-01
2 participants
@BrentOzar @Jhiggin
@BrentOzar
Copy link
Member

BrentOzar commented Jan 13, 2019

Is your feature request related to a problem? Please describe.
When I'm doing index tuning, I end up dropping indexes, plus I want to have an undo script to create them again if something goes wrong.

Describe the solution you'd like
For @mode = 2, add columns for Drop Index and Create Index, populated with the commands to drop and recreate separately.

Are you ready to build the code for the feature?
volunteer

@BrentOzar BrentOzar added enhancement sp_BlitzIndex labels Jan 13, 2019

@BrentOzar BrentOzar added this to the Ivor peered above a spectacle milestone Jan 13, 2019

@BrentOzar BrentOzar self-assigned this Jan 13, 2019

@Jhiggin

This comment has been minimized.

Sign in to view
Copy link
Contributor

Jhiggin commented Jan 17, 2019

Hey @BrentOzar , Just dropping in as requested from Slack. Seems like a pretty straight-forward change. One question I do have as far as for visuals. If the table is a heap, would you say that the Drop Tsql column and the Create Tsql column should be blank or would you prefer to have a string in the column? Example below for reference.

image
@BrentOzar

This comment has been minimized.

Sign in to view
Copy link
Member Author

BrentOzar commented Jan 17, 2019

Oh great question - if it's a heap, yeah, it should be blank. Perfect! Thanks sir.

@BrentOzar BrentOzar removed their assignment Jan 17, 2019

@BrentOzar

This comment has been minimized.

Sign in to view
Copy link
Member Author

BrentOzar commented Jan 17, 2019

Oh dang it, I can't assign you in here for some reason. Ah, at least people who find it will see that you're working on it!
@Jhiggin

This comment has been minimized.

Sign in to view
Copy link
Contributor

Jhiggin commented Jan 17, 2019

No worries. Thanks again.

Jhiggin added a commit to Jhiggin/SQL-Server-First-Responder-Kit that referenced this issue Jan 17, 2019

@Jhiggin
BrentOzarULTD#1921 Sp_BlitzIndex Add Drop and Create Columns to Output 
Joined output query to #IndexCreateTsql to retrieve the CreateTsql already generated earlier in the script.  Created Drop TSQL based off evaluating what type of index was present.
0ed2ea9
@Jhiggin

This comment has been minimized.

Sign in to view
Copy link
Contributor

Jhiggin commented Jan 17, 2019

Pull request has been generated for code changes. Let me know if this will work. This is my first time trying to contribute to a project. But believe I covered everything required.

BrentOzar added a commit that referenced this issue Jan 17, 2019

@Jhiggin @BrentOzar
#1921 Sp_BlitzIndex Add Drop and Create Columns to Output (#1923) 
Joined output query to #IndexCreateTsql to retrieve the CreateTsql already generated earlier in the script.  Created Drop TSQL based off evaluating what type of index was present.
c3f5f15
@BrentOzar

This comment has been minimized.

Sign in to view
Copy link
Member Author

BrentOzar commented Jan 17, 2019

Great job, looks good! Merged, and I'll credit you in the next release. Thanks! That's a lot better than my first pull request went, hahaha!

BrentOzar added a commit that referenced this issue Jan 17, 2019

@BrentOzar
#1921 sp_BlitzIndex adding drops 
Moving drop and create TSQL to the end of mode 2's results. Closes #1921.
26dee29

@BrentOzar BrentOzar referenced this issue Jan 17, 2019

Merged

#1921 sp_BlitzIndex adding drops #1924

@BrentOzar BrentOzar closed this in #1924 Jan 17, 2019

BrentOzar added a commit that referenced this issue Jan 17, 2019

@BrentOzar
#1921 sp_BlitzIndex adding drops (#1924) 
Moving drop and create TSQL to the end of mode 2's results. Closes #1921.
Verified
This commit was created on GitHub.com and signed with a verified signature using GitHub’s key.
GPG key ID: 4AEE18F83AFDEB23 Learn about signing commits
270585d

BrentOzar added a commit that referenced this issue Jan 28, 2019

@BrentOzar
2019-01 Release (#1932) 
* Updating readme.md for sp_DatabaseRestore

Documenting @ExistingDBAction for https://dba.stackexchange.com/questions/226145/sp-databaserestore-msg-50000.

* 1900 sp_BlitzIndex add histograms

When @TableName is specified and sys.dm_db_stats_histogram is available. Closes #1900.

* #1903 sp_Blitz SQLServerCheckup

Adding filter for that app name. Closes #1903.

* #1905 sp_BlitzIndex remove BOU link

Nothing against BOU, just don't need it in that particular place. Closes #1905.

* #1908 Update copyright dates

Ah, the glamour. Closes #1908.

* Issue #1904 Change RAISERROR 'severity' for that should trigger throw and error.

* Issue #1910 Add SQL Server version check before choosing 'memory grant' as the @BlitzCacheSortOrder.

* Issue #1910 Add temp table creation.

* #1914 sp_BlitzIndex partition error severity

Dropping severity level from 16 to 0 since we're logging it in the result set anyway. Closes #1914.

* Issue 1894 Moved RESTORE HEADERONLY up

* LF line endings

* added some extra checks + corrected some nesting

* rebase and line endings

* Web site commit

Does this fix line endings?

* #1916 sp_Blitz ignore backup on TempDB drive

Closes #1916.

* Auto line endings

Dealing with sp_DatabaseRestore's line endings.

* 1919 sp_Blitz ignoring a few checks for Managed Instances (#1920)

* 1919 sp_Blitz ignoring a few checks for Managed Instances

Working on #1919.

* #1919 sp_Blitz excluding restored databases

When checking backup history. Working on #1919.

* #1919 sp_Blitz ignore some checks on Mgd Instances

Changing RCSI wording on Azure SQL DB. Working on #1919.

* #1921 Sp_BlitzIndex Add Drop and Create Columns to Output (#1923)

Joined output query to #IndexCreateTsql to retrieve the CreateTsql already generated earlier in the script.  Created Drop TSQL based off evaluating what type of index was present.

* #1921 sp_BlitzIndex adding drops (#1924)

Moving drop and create TSQL to the end of mode 2's results. Closes #1921.

* #1925 sp_Blitz AWS RDS detection (#1926)

Don't just rely on EC2 VM name to detect RDS. Also adds a new result noting that checks were skipped. Closes #1925.

* #1927 sp_BlitzIndex skip rdsadmin db (#1928)

GetAllDatabases = 1 fails when it hits rdsadmin because they're referring to the resource db. Closes #1927.

* 2019_01 Release (#1931)

Prep work for the release - changing version numbers, building build scripts.
Verified
This commit was created on GitHub.com and signed with a verified signature using GitHub’s key.
GPG key ID: 4AEE18F83AFDEB23 Learn about signing commits
30f5b42
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment