How to Rename a Database

If you ever need to rename a database, I recommend using the following script. It ensures no other users are connected. This is important, otherwise your connection won’t be able to gain a lock. I hope that helps!

Replace Text in a Database

If you need to perform an update on some text value in your database, you can easily get the job done using the REPLACE() function. I strongly suggest using the WHERE clause. You don’t want to accidentally update a row you didn’t mean to. I…

Use RowCount to determine query success

If you have a Stored Procedure that populates a temporary table, and you want to ensure the temporary table has been populated with something, you could use @@Rowcount. It’s an in-build property, and can be used as an alternative to selecting COUNT(1) from the temporary…

Setting NOCOUNT in your Stored Procedures

NOCOUNT is a SQL Server setting that (when activated) will stop SQL Server from returning the “x row(s) affected” message as part of its execution. Although this message is helpful with T-SQL queries in SQL Server Management Studio, it is of little use with Stored…

Excluding FILESTREAM when backing up a Database

When using a script to backup a SQL Server database, it can be tricky to exclude the filestream. But, you can exclude filestream by only including the Primary file group, which typically only includes the data and log files. I hope this helps.

Temporary Tables – Local vs Global

Hi all, I just wanted to share a link that has a great explanation on the difference between a local temp table, and a global one. Check it out here! And if you can’t be bothered checking it out, I’ll summarize.. A local temp table…

View physical file path of all Databases

I recently stumbled across a .mdf and .ldf file for a database while trying to clear up space on a work machine. Although the database did have a name, I couldn’t find a database attached to the SQL Server instance that matched that name. So,…

Access active session data

If you want to check the properties of any active sessions on a database, you can with the following script. You need to run it while connected to the database you wish to query. You can also easily filter the results using login_name to find…

Accessing database options

If you ever need to check details about a database, you can do so with the following command: The results include Database size Owner Date created Status summary Compatibility level There are also details about the physical location of the database files. You can also…

Check settings for stored procedures

There may be times you need to view database settings at the stored procedure level. As an example, I have been working with filtered indexes which require the QUOTED_IDENTIFIER setting to be enabled. Therefore stored procedures that insert or update a table that has a…