Useful T-SQL queries and scripts to work in SQL Server

This post is about some queries and scripts that I use in my daily work on SQL Server. Some of them are self-explanatory, i.e. the name of the query/script already describes its purpose. However, if you need to get some more details around, then you may need to add some extra columns to the output results. I often put the output from the queries into temp tables so that I’m able to do an extra querying with other meta-data sets. Some of the queries use a threshold parameter which you can change for your needs. Some others have the opposite version like instead of EXISTS you replace with NOT EXISTS and get another output.

For most of the queries/scripts, you’ll need to have sysadmin permissions over the databases.

Query 1: List databases with size info

Query 2: List objects with space info

Query 3: Find the average size of rows in tables

Query 4: Get fragmentation info for the tables

Query 5: Recommendation for potentially missing indexes. Note: The indexes analysis requires more time, so you may run this query scheduled, saving the output results for a further deeper analysis.

Query 6: Find TOP 50 unused indexes in a database. Note: Make sure the SQL Server hasn’t been restarted for a longer period.

Query 7: Tables with INSTEAD OF triggers

You can use NOT EXISTS in the WHERE clause to find the tables not having INSTEAD OF triggers.

Query 8: Tables that don’t have Primary Key

You can easily find the tables with primary keys if you just use EXISTS in the WHERE clause.

Query 9: Find objects that use compression

Script 10: Recompile all programmable objects in a database

Script 11: Refresh all views in a database

Query 12: Find all constraints that need to be entrusted. The last column of the result set is the SQL command to execute to entrust the constraint.

The version of the query with the cursor (script) so that it entrusts all constraints automatically:

Script 13: Kill all user processes for a database. Note: Be careful with this stored procedure! I usually use in the Test/Dev environments.

Query 14: List all assemblies in a database

Query 15: Check if your dynamic T-SQL statement is valid. Note: I create this stored procedure and use in my scripts and codes.

Query 16: All user-created statistics

Query 17: Tables with more than 30 columns (wide tables)

Query 18: Tables with more than 5 indexes

Query 19: Tables without a Clustered Index (Heap)

You can easily find the tables with Clustered indexes by just using EXISTS in the WHERE clause.

Query 20: Tables with their rows (fastest way to get tables rows)

Query 21: Tables with XML columns

Query 22: Tables with at least one LOB (max) column

Query 23: Tables with at least one TEXT, NTEXT, IMAGE column

Query 24: Tables with Identity columns

You can easily find the tables without identity columns by just using NOT EXISTS in the WHERE clause.

Query 25: Tables with at least two triggers

Query 26: Tables dependency order

Query 27: View server roles and permissions per Login

Script 28: Re-align the identity of the tables. If the alignment of Identity matters to your business logic then you go with this script. However, keep in mind the cases when you may have the Lost Identity especially when there is often switching in the clustered environments.

Script 29: Script to create a database snapshot. Note: Be careful with the databases snapshots. They could slow-down the database activities.

You can find it in this link
I usually use the script to create database snapshots temporarily on the Test/Dev servers.

Script 30:

I always use Exec sp_WhoIsActive in the daily work. You can get the amazing stored procedure here.

Script 31:

I use this bunch of scripts by Bernt Ozar as well.

Thanks for reading. You can post some other useful queries and scripts in the comment 😉

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.