SQL-Server-Helper-Script

This repository contains all helping SQL Server Script which will use in day to day life to troubleshoot and understand SQL Server Health.

1. Select Primary Key value from Table dynamically-Sql Server
2. Find Blocking queries in Sql Server
3. Find Most Expensive Stored Procedure Sql Server
4. Find most expensive queries-Sql Server
5. Find most expensive tables-Sql Server
6. Find most used column names in database – Sql Server
7. Find out all tables without indexes in Sql Server
8. Find redundant indexes Sql Server
9. Find unused Indexes – Sql Server
10. Generate CREATE Index script for all indexes in database in Sql Server
11. Get all available Databases in SQL Server Instance
12. Get all schemas in SQL Server database
13. Find the total size of the Indexes Script - SQL Server
14. Who has CONNECT permission for an endpoint script - SQL Server
15. Find last restarted time of SQL Server
16. Find stored procedures which have used Temporary Table
17. Find stored procedures which have used Dynamic Sql
18. find a list of Weekends between given two Dates in SQL Server
19. Script to take backup of all database
20. Find the largest Objects along with Indexes size - SQL Server
21. Find size of Tables in sql server
22. Find the list of Triggers script - SQL Server
23. Check the file exists or not (By Using xp_fileexist ) Script - SQL Server
24. Find the Service Account Name Script - SQL Server
25. Get list of CHECK Constraints of Database Script - SQL Server
26. Find Unused Index Script - SQL Server
27. Find Database USER LOGIN Information Script - SQL Server
28. Find total no of sessions for each Databases - SQL Server
29. Find total number of Sessions of a Database - SQL Server
30. Find the source of connection leaks script - SQL Server
31. Find waiting Queries which are block by other running Queries Script - SQL Server
32. Find Most Expensive Stored Procedure Script - SQL Server
33. Find top 10 most CPU utilizing Queries - SQL Server Script
34. Most expensive queries - SQL Server Script
35. Find Blocking Queries - SQL Server Script
36. Find Memory Utilization for each database - SQL Server Script
37. Get the CPU Information from Registry - SQL Server Script
38. Generate Shrink Script for each Database File - SQL Server Script
39. Find Memory usage and allocation - SQL Server Script
40. Drop all nonclustered Indexes - SQL Server Script
41. Find disable foreign key tables - SQL Server Script
42. Find Large Object Data Type Columns - SQL Server Script
43. Find a Table which has more than N columns - SQL Server Script
44. Find a Table which has more than N Indexes - SQL Server Script
45. Sql Server Maintenance Script, includes rebuild & reorganize index and update statistics
46. Get all NonClustered Indexes from Database - SQL Server Script
47. Get all Clustered Indexes generated with primary keys only from Database - SQL Server Script
48. Get all Clustered Indexes from Database - SQL Server Script
49. SQL SERVER Script: Get all NonClustered Indexes which are with Unique Key constraint only from Database
50. SQL SERVER Script: Get all NonClustered Indexes list which are with Unique Key constraint only from Database
51. Find database size - SQL Server Script
52. Check Newly Allocated Pages to the Table - SQL Server Script
53. Drop a Table from all Databases - SQL Server Script
54. Rename all Tables of a Database - SQL Server Script
55. Check the status of running Database Backup - SQL Server Script
56. Find total number of Sessions - SQL Server Script
57. Get the definition of Trace Script - SQL Server
58. Check If Mirroring enable for Database Script - SQL Server
59. Find fragmentation of Columnstore Indexes
60. Find the Location of Database Backup Files - SQL Server Script
61. Find all Columns information of a Database - SQL Server Script
62. Find all Default values with Columns - SQL Server Script
63. Find Last Backup Time for All Databases - SQL Server Script
64. Find a different Server Property - SQL Server Script
65. Find usage of Database Indexes - SQL Server Script
66. Find Index Operational Stats - SQL Server Script
67. Find Open transactions in SQL Server
68. Find last DDL changes in SQL Server
69. Find Running Process and Session in SQL Server
70. Find Foreign key References in SQL Server
71. Find out most recently modified Stored Procedure and Table in SQL Server
72. Find CPU usage per Different Objects - SQL Server Script
73. Find Slowest Running Query in SQL Server
74. Find Cross Database Object Dependency in SQL Server
75. Find Object Dependency in SQL Server
76. sys.dm_column_store_object_pool to find memory pool usage by Columnstore Indexes - SQL Server 2016
77. sys.column_store_row_groups to find information on Columnstore row groups - SQL Server 2014
78. Find Outdated Statistics - SQL Server Script
79. SQL Server Script to kill all processes for Database
80. Find Index Average Fragmentation in Percentage - SQL Server Script
81. Find Missing Indexes - SQL Server Script
82. SQL Server 2016 Script to get information of Cached Functions with Total Execution Time
83. SQL Server 2014 Monitor Memory Optimized Tables which stored in Memory
84. Script to find the Forwarded Records count of a Table - SQL Server
85. Use sp_MSforeachtable to apply a T-SQL command to every table of selected Database in SQL Server
86. SQL Server Script to find invalid Foreign Keys
87. SQL Server Script to check the size of Query Plan Cache
88. SQL Server script to find dirty pages from Memory, when DBCC DROPCLEANBUFFERS fails
89. SQL Server Script to find SOS_SCHEDULER_YIELD wait type usage
90. SQL Server Script to check the Allocation Units of a Table
91. SQL Server Script to execute Update Statistics for all Databases
92. SQL Server Script to find Open Transaction to avoid Deadlock
93. SQL Server Script to identify current Authentication Mode
94. SQL Server script to find Log Sequence Number (LSN) from Database Backup Files
95. Script to change Schema of all the Tables SQL Server
96. SQL Server Script to enable and disable Auto Create Statistics
97. SQL Server script to Enable and Disable Auto Update Statistics
98. SQL Server Script to Enable and disable Auto Update Statistics Asynchronously
99. SQL Server Script to find all System Objects related to File Table
100. SQL Server script to find auto created Statistics Objects
101. SQL Server Script to Find Statistics Information of the Database Objects
102. SQL Server Script to find waiting Queries which are block by other running Queries
103. SQL Server Script to find top 10 Stored Procedure which are utilizing more CPU
104. SQL Server Script to find all Running SQL Server Agent JOB
105. SQL Server script sp_updatestats to update the Database Statistics
106. SQL Server script to Find the total row count and size of the Tables
107. SQL Server Script to find the size of Database Files and Log Files
108. SQL Server Script to find total Row Count of All Tables in a Database
109. SQL Server Script to find CPU Pressure using Signal Wait Time
110. SQL Server Script to Hide all Databases in SSMS
111. SQL Server Script to find Status of all Database Trigger
112. SQL Server Script to List all Dynamic Management Views and Functions
113. SQL Server Script to find Identity Column of a Database
114. SQL Server Script to find all the Tables without having a Primary Key
115. SQL Server Script to find Databases from all the Instances
116. SQL Server Script to find PORT information of an Instance
117. SQL Server Script to monitor the Corrupt Database Pages
118. SQL Server Script to find bad Sessions or Processes
119. SQL Server Script to find Open Connections and CPU Usage of each Client Programs
120. SQL Server Script to find last statement executed by a SPID using sys.dm_exec_input_buffer
121. SQL Server Script to find all Backup History Information
122. SQL Server Script to make Database Read Only
123. SQL Server Script to make Database Read Write
124. SQL Server Script to check all Database Autogrowth Settings
125. SQL Server Script to find and change Compatibility Level of Database
126. SQL Server Script to find Estimated Finish Time of The Backup Database
127. SQL Server Script to find Estimated Finish Time of The Restore Database
128. SQL Server Script to find uncontained objects of a Database
129. SQL Server: Script to Enable and Disable XP_CMDSHELL Configuration
130. SQL Server 2012 Introduced dm_db_log_space_usage to find a Log size
131. SQL Server Script to change the Recovery Model of a Database
132. SQL Server Script to find the status of Trace is running or not
133. SQL Server Script to find Installation Date, time and Authentication Mode
134. SQL Server Script to get all tables from database
135. Remove Cross duplicate rows in SQL Server

Please Note: Folks, I request you to test shared scripts in your development or QA environment before executing on live instance.