t-sql – Page 66

Delete Database Mail Messages from the msdb Database in SQL Server (T-SQL)

September 2, 2020September 1, 2020 by Ian

When you use Database Mail to send emails from SQL Server, email messages and their attachments are stored in the msdb database. You should periodically delete these messages to prevent the database from growing to large.

To delete these messages with T-SQL, use the sysmail_delete_mailitems_sp stored procedure.

You can delete mail messages based on their send request date (i.e. before a certain date) or based on their status. You can also delete all mail messages by using the current date as the request date.

How to Change the Configuration Settings for Database Mail in SQL Server (T-SQL)

August 31, 2020 by Ian

If you’re using Database Mail in SQL Server, you can change the configuration settings by executing the sysmail_configure_sp stored procedure in the msdb database.

How to Check the Configuration Settings for Database Mail in SQL Server (T-SQL)

September 1, 2020August 30, 2020 by Ian

If you’re using Database Mail in SQL Server, you’ll probably want to check your configuration settings at some stage.

You can do this by executing the sysmail_help_configure_sp stored procedure in the msdb database.

Show Advanced Server Configuration Options in SQL Server (T-SQL)

August 29, 2020 by Ian

If you’re trying to check an advanced configuration option in SQL Server, but you get an error, you might find the following helpful.

Examples of Formatting ‘datetimeoffset’ in SQL Server using Standard Format Strings (T-SQL)

January 28, 2021August 24, 2020 by Ian

I thought it would be interesting to run a few quick queries to see how various formatting strings affect the formatting of date/time values.

How to Use GOTO in SQL Server

March 6, 2020 by Ian

In SQL Server, you can use GOTO to alter the flow of execution. You can use it to “jump” to another part in the T-SQL code.

The way it works is, you create a label, then you can use GOTO to jump to that label. Any code between GOTO and the label are skipped, and processing continues at the label.

GOTO statements and labels can be used anywhere within a procedure, batch, or statement block. They can also be nested.

Difference Between sys.sql_modules, sys.system_sql_modules, & sys.all_sql_modules in SQL Server

March 6, 2020 by Ian

In SQL Server the sys.sql_modules, sys.system_sql_modules, and sys.all_sql_modules system catalog views return metadata about SQL language-defined modules in SQL Server.

However, there is a difference between them.

Difference Between Local and Global Temporary Tables in SQL Server

March 5, 2020 by Ian

When you create a temporary table in SQL Server, you have the option of making it a local or global temporary table.

Here’s a quick outline of the main differences between local temporary tables and global temporary tables.

5 Ways to List Temporary Tables using T-SQL

November 15, 2025March 4, 2020 by Ian

In SQL Server, you can use any of the following five ways to return a list of temporary tables using Transact-SQL.

These return both local and global temporary tables.

Create a Temporary Table Based on Another Table in SQL Server

March 3, 2020 by Ian

In SQL Server you can create a temporary table based on another table by using the SELECT... INTO syntax.

You can create the table with or without data. In other words, you can copy data from the original table if you wish, or you can create the table without any data.