SQL tricks are a set of useful SQL scripts everybody supporting MS SQL DBs should have at hand. Scripts are grouped into folders based on specific functionality:
- CRUD
- INSERT INTO SELECT to copy all or specific columns from one table to another one
- drop a database
- Data integrity
- disable / enable DB integrity check
- turn off / on identity check for auto incremental columns (it is required when a specific value needs to be inserted during data import as a record Id for example)
- DB backup
- create a DB backup, restore a DB from a backup
- DB Id
- retrieve a DB Id
- retrieve Id for all DBs at once
- DB Users
- autofix a DB user after a DB recovery
- change a DB owner
- Indexes
- rebuild indexes (including a system sp_MSforeachtable procedure in case it is missing in Azure environment)
- Info
- select a product version
- select all tables from a certain DB that contain a specific column name
- JSON
- create a computed column based on a JSON property
- get a value of a JSON property into a local variable
- use JSON properties in a query
- Log file
- shrink a log file
- Server resource utilization
- memory usage by a table
- buffer cache consumption by DBs / page types
- Spatial data
- create a geography column in a table
- Statistics
- review statistics
- update statistics
- review an execution plan
- Transactions
- catch long-running or uncommitted transactions
- return information on open transactions
- Triggers
- disable / enable all table triggers
- Validation
- check DB objects (stored procedures, views, functions, tables, triggers) for compatibility with DB schema
Hope you found these scripts helpful, your contributions and suggestions will be very much appreciated. Please submit a pull request.
SQL tricks are released under the MIT license what means that you can modify and use scripts how you want even for commercial use. Please give this repository a star if you like any of SQL tricks and your experience was positive.