Change auto-named SQL constraint

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

Clean and shrink SQL database

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

Fix SQL database user mapping

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.

Continue reading Fix SQL database user mapping

SearchAllTables SQL Stored Procedure

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

sp_generate_inserts Azure 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