SQL Archives - Jonathan Crozier

Query to list the number of rows for each table in a SQL Server database

When working with a SQL Server database, I often find it helpful to have a SQL query to hand that can be executed to tell me how many rows exist within each database table. This can be useful in scenarios where records are being inserted into lots of tables and you need to check that…

How to rename a SQL Server machine

InSQL

Have you ever installed an instance of SQL Server onto a new workstation or server and subsequently realised that you forgot to rename the machine first? In these cases, you’ll likely have installed both SQL Server and SSMS (SQL Server Management Studio). After launching SSMS you happen to notice that the ‘Server name’ field is…

Query to list all indexes in a SQL Server database

InSQL

Whenever I’m analysing the performance of a SQL Server database, I often find myself needing to retrieve a list of all indexes for review. Missing or unsuitable indexes are a leading cause of SQL Server slowdowns, so it can be very useful to view the existing indexes to get an idea of how heavily indexed…

Preventing SQL injection in C# applications

InProgramming Security SQL

SQL injection is a common attack vector that remains one of the most prevalent security risks for applications today. One of the reasons SQL injection attacks are so pervasive is the fact that injection vulnerabilities are very easy for attackers to discover and exploit, sometimes with devastating consequences. On the other hand, mitigating SQL injection…

SQL Server upsert with condition and returned value

InSQL

Sometimes when working with a SQL Server database, you need to update a specific table row or insert a new row if the one you were trying to update isn’t already there. This logic is known as an ‘upsert’ operation i.e. either update or insert a row depending on whether it currently exists. In this…

SQL Server Database Mail setup via SQL

InSQL

Setting up SQL Server Database Mail is relatively simple when using SQL Server Management Studio, thanks to the graphical setup wizard it provides for most SQL Server editions. However, SQL Server Express, the free edition of SQL Server, does not offer the facility to configure Database Mail via a user interface. Thankfully, once you understand…

SQL Server Database Mail setup simplified

InSQL

Having the facility to send emails directly from your database engine is a hugely useful feature and this is exactly what the Database Mail functionality in SQL Server provides. With Database Mail, you can send email alerts when specific database events occur, or send query results and reports to users automatically on a scheduled basis….

Find out how many IDs are left for your SQL Server database tables

InSQL

If you are administering a very large SQL Server database that uses integer IDs, you may have cause to be concerned about how many identity values you have left for your tables. This is a particular concern for heavy transactional tables where new rows are constantly being inserted and there are other reasons why identity…

Connecting to SQL Server from PowerShell

InScripting SQL

PowerShell is an awesome scripting language with many powerful features. This includes integration with SQL Server; a fantastic database engine that is both reliable and battle-tested. If you need to automate repetitive and/or time-consuming DBA processes, connecting PowerShell to your SQL Server instance can be a massive help. In this article, we are going to…

SQL query things you keep forgetting

InSQL

When working with SQL databases, you may find there are things you need to do which although seemingly basic on the surface, are easy to forget due to nuances in query syntax and the required ordering of statements. For these situations, it is useful to have a working example to refer to which reminds you…

Category: SQL

Query to list the number of rows for each table in a SQL Server database

How to rename a SQL Server machine

Query to list all indexes in a SQL Server database

Preventing SQL injection in C# applications

SQL Server upsert with condition and returned value

SQL Server Database Mail setup via SQL

SQL Server Database Mail setup simplified

Find out how many IDs are left for your SQL Server database tables

Connecting to SQL Server from PowerShell

SQL query things you keep forgetting

Query to list the number of rows for each table in a SQL Server database

How to get the Local AppData directory for all Windows users within a .NET application

Using JavaScript to copy text to the clipboard