If you’ve ever tried to change a default value of a SQL constraint you’ve probably noticed that you have to drop the constraint, and then re-create the constraint with a new default value. However, what if you found an auto-named SQL constraint, and you started wondering how your SQL script will work across different environments with different databases…Continue reading Change auto-named SQL constraint
Sometimes you need to find database or tables sizes to find out what is taking the most disk space in a Microsoft SQL database server. Following two queries can be very handy in such case. Result looks following.Continue reading SQL database and tables sizes
Sometimes you need to clean and shrink SQL database, since once database grows even if all records get deleted, the database file would still take the space as before clean up. To achieve this we can manually delete records and call SHRINKFILE command. However, it’s important to delete records in small batches in transaction not to clog up the database server.Continue reading Clean and shrink SQL database
RefreshViews is simple stored procedure to refresh all database views in a Microsoft SQL database server. This is especially useful when working with legacy systems that have select * from some_view like queries in views, and needs updating after adding/removing columns.Continue reading SQL RefreshViews
SQL quick row count script gets row counts for all tables in a database in Microsoft SQL database servers. Result looks following.Continue reading SQL Quick Row Count
If you used a SQL login and needed to restore a database (dev/test), you should notice that your SQL login doesn’t work for the database anymore. The reason for this is that SQL login and database user mapping was lost. Even you can see the user name in database user’s mapping, however in internal SQL memory the restored database’s user has different reference than the SQL login.
Sometimes you need to find a specific string or value in a database that you are not familiar with. You may not know where to start since a completely new database for you, or there are just way too many tables. In such cases there is a very handy stored procedure that searches for a string through all the tables.Continue reading SearchAllTables SQL Stored Procedure
The best practice to run SQL queries is to run them in transaction and wrap with SQL’s Try … Catch statement, so we could rollback the transaction, and throw an exception. Below is the code snippet that would do it all.Continue reading Try … Catch in SQL
One more useful SQL code snippet – sp_generate_inserts. This stored procedure (SP) generates SQL insert statements of existing data from the table specified. It is a very handy script when you have limited options to make a database backup file, but still need to export data.
This script was originally written by Narayana Vyas Kondreddi back in 2002, however his website is not accessible any more. Therefore I had to slightly modify the script to work on Azure SQL databases, since Azure SQL has some limitations/changes compared to classical MSSQL.Continue reading sp_generate_inserts Azure SQL
MSSQL server index fragmentation check code snippet I had in my archive. It shows what indices would be good to add, and also shows the most CPU intense queries. A nice SQL optimization toolkit.Continue reading Index fragmentation in MSSQL Server