How does Database Mail work?
The SQL Server Database Engine may send emails using Database Mail, an enterprise email solution. Your database applications can send users emails by using Database Mail. The messages may include files from any resource on your network in addition to query results.

Step 1: In order to send mail from SQL Server, we must first install Microsoft SQL Server Management Studio (SSMS) on our PC or laptop. Thus, download and install SSMS first if you haven't already on your PC.

Step 2
Now, Open SSMS and connect it to your SQL Server instance.
Expand Management option, there you can find the Database Mail option. As you can see in the following image.

Now, Right click on Database Mail and select the Configure Database Mail option.
This will open a Database Mail Configuration wizard. Select next to continue.
Now choose the first option, Set up Database Mail, by performing the following tasks, and click next.

Now, give your database mail Profile Name and its description as whatever you want. Here, I am giving "Test Profile" as the profile name. In the SMTP account section, select the Add account option to create a new SMTP account as in the following image.

Now, enter the information shown in the following image in the New Database Mail Account box. I'm sending emails from this account using Gmail. Thus, I set the server name to smtp.gmail.com and the port number to 587.

Use the email address and password from which you wish to send emails when logging into SMTP Authentication and Outgoing Mail Server. For security reasons, confirm that the "The server requires a secure connection" check box is checked as well. When you are finished, click OK. To move on to the next screen, click next.

Step 3. The next screen is about making a Database mail profile, either public or private. You can leave this option and move to the next screen. Again, select next to finish the creation process for the Database Mail profile.

Now you are all set up to send e-mail. Now go to Management, right-click on Database Mail, and select Send Test E-mail... option to send test mail as in the following image.

Choose Test Profile as Database Mail Profile from Dropdown and enter To email address, Subject, and Body, and click Send Test E-Mail. Now your e-mail has been sent successfully from SQL Server.

Step 4. If you want to send mail using SQL query, then you can use the system database 'msdb' and its stored procedure 'sp_send_dbmail' as follows.
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Test Profile',
    @recipients = '[email protected]',
    @subject = 'Test Mail',
    @body = 'This is the test mail.',
    @body_format = 'HTML';

To check the status of the e-mail you sent, you can execute the following query in SQL Server.
USE msdb;
GO

SELECT *
FROM sysmail_allitems;

In this query result, you can find all the details about the mail sent from the SQL Server. You can check the sent_status column to check the status of mail.
So, this is all about using the database mail in the SQL Server to send e-mails. I hope you understood my explanation and liked this article. Thank you!

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

There is much to look forward to as we eagerly await the introduction of the next edition of SQL Server, which is scheduled for about 2025. Microsoft has been making references to a number of intriguing new features and enhancements that should boost our analytics and database management skills. This is a preview of what's to come:

1. Improved Data Analysis
The enhanced connectivity with Azure Synapse Analytics is one of the most eagerly awaited improvements. This will allow for almost real-time analytics on operational data, which will facilitate the acquisition of knowledge and speedy decision-making based on data.

2. Integration of Object Storage
S3-compatible object storage is anticipated to be supported by the upcoming release. Better data virtualization and direct T-SQL query support for parquet files translate to more opportunities for data management and analysis.

3. Enhanced Accessibility
Features like contained availability groups and disaster recovery replication to an Azure SQL Managed instance are to be expected. More reliable alternatives for preserving high availability and guaranteeing business continuity will be made available by these improvements.

4. Machine Learning and AI
Given AI's increasing significance, improved AI-related functionality will probably be included in the upcoming SQL Server version. In order to improve performance and facilitate the integration of Machine Learning models into your data operations, this may include utilizing GPUs.

5. Enhancements in Performance
As always, performance is the main priority, and the next version is no different. Keep an eye out for improvements to the Maximum Degree of Parallelism (MaxDOP) for query execution and optimizations related to cardinality estimates. These adjustments should lead to faster and more effective searches.

6. Improvements to Security
Security is still of the utmost importance, and the new version will include capabilities to support compliance objectives and data protection. These improvements will guarantee the security of your data and make it easier for you to comply with regulatory standards.

7. Support for Regular Expressions
Lastly, support for regular expressions is one of the most desired features. This will significantly improve the text data manipulation capabilities of SQL Server, facilitating the execution of intricate text searches and transformations.

With these additions, SQL Server's upcoming release should become a very useful tool for developers and data specialists. As the release date approaches, be sure to check back for additional updates!

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

SQL Server triggers are special stored procedures that are intended to run automatically in response to specific database events. These events could include activities related to data definition, like CREATE, ALTER, or DROP, as well as actions related to data manipulation, like INSERT, UPDATE, or DELETE. There are two main categories of triggers.

• DML Triggers (Data Manipulation Language Triggers): These triggers activate in response to DML events, which encompass operations like INSERT, UPDATE, or DELETE.
• DDL Triggers (Data Definition Language Triggers): These triggers activate in response to DDL events, which include operations such as CREATE, ALTER, or DROP.

When Is a Trigger Useful?
In a variety of situations, triggers are employed to guarantee that particular actions are carried out automatically in reaction to particular database events. Here are a few such scenarios when triggers are useful.

• Audit Trails
  
  • When: You need to track changes to important data for compliance, security, or historical analysis.
  • Why: Triggers can automatically log changes to an audit table without requiring additional application code, ensuring consistent and reliable tracking.
• Enforcing Business Rules
  • When: Business rules must be enforced directly at the database level to ensure data integrity and consistency.
  • Why: Triggers ensure that business rules are applied uniformly, even if data is modified directly through SQL queries rather than through an application.
• Maintaining Referential Integrity
  • When: You need to ensure that relationships between tables remain consistent, such as cascading updates or deletes.
  • Why: Triggers can automatically handle referential integrity tasks, reducing the risk of orphaned records or inconsistent data.
• Synchronizing Tables
  • When: You need to keep multiple tables synchronized, such as maintaining a denormalized table or a summary table.
  • Why: Triggers can automatically propagate changes from one table to another, ensuring data consistency without manual intervention.
• Complex Validations
  • When: Data validation rules are too complex to be implemented using standard constraints.
  • Why: Triggers can perform intricate checks and validations on data before it is committed to the database.
• Preventing Invalid Transactions
  • When: Certain operations should be blocked if they don't meet specific criteria.
  • Why: Triggers can roll back transactions that violate predefined conditions, ensuring that only valid data modifications are allowed.

Execution of trigger

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

With the use of SQL's ranking functions, you may give each row in a result set's partition a unique ranking. These functions come in very handy when you have to determine the row order according to certain standards. The RANK(), DENSE_RANK(), and ROW_NUMBER() functions are the three main ranking functions. Despite their apparent similarity, they exhibit different habits, particularly with regard to handling ties. We'll examine these functions in-depth and provide a useful example to illustrate how they differ in this post.

RANK()
The RANK() function assigns a unique rank to each row within a partition of a result set, with gaps in the ranking sequence where there are ties. This means that if two or more rows have the same value in the ordering column(s), they will be assigned the same rank, but the next rank will be incremented by the number of tied rows.

Syntax
RANK() OVER (
    PARTITION BY column1, column2, ...
    ORDER BY column1, column2, ...
)

Example
SELECT
    Name,
    Score,
    RANK() OVER (ORDER BY Score DESC) AS Rank
FROM
    Students;

Result

| Name    | Score | Rank |
|---------|-------|------|
| Alice   | 95    | 1    |
| Bob     | 85    | 2    |
| Charlie | 85    | 2    |
| Dave    | 75    | 4    |
| Eve     | 70    | 5    |

In this example, Bob and Charlie have the same score and are both ranked 2nd. The next rank, assigned to Dave, is 4th, leaving a gap at rank 3.

In this example, Bob and Charlie have the same score and are both ranked 2nd. The next rank, assigned to Dave, is 4th, leaving a gap at rank 3.
DENSE_RANK()

The DENSE_RANK() function is similar to RANK() but without gaps in the ranking sequence. When rows have the same value in the ordering column(s), they receive the same rank, but the next rank is incremented by one, regardless of the number of ties.

Syntax
DENSE_RANK() OVER (
    PARTITION BY column1, column2, ...
    ORDER BY column1, column2, ...
)

Example
SELECT
    Name,
    Score,
    DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank
FROM
    Students;

Result
| Name    | Score | DenseRank |
|---------|-------|-----------|
| Alice   | 95    | 1         |
| Bob     | 85    | 2         |
| Charlie | 85    | 2         |
| Dave    | 75    | 3         |
| Eve     | 70    | 4         |

Here, Bob and Charlie are both ranked 2nd, but the next rank is 3rd, assigned to Dave, with no gaps in the ranking sequence.

ROW_NUMBER()
The ROW_NUMBER() function assigns a unique sequential integer to rows within a partition, without considering ties. Each row gets a distinct number, even if there are ties in the ordering column(s).
Syntax
ROW_NUMBER() OVER (
    PARTITION BY column1, column2, ...
    ORDER BY column1, column2, ...
)

Example
SELECT
    Name,
    Score,
    ROW_NUMBER() OVER (ORDER BY Score DESC) AS RowNum
FROM
    Students;

Result

| Name    | Score | RowNum |
|---------|-------|--------|
| Alice   | 95    | 1      |
| Bob     | 85    | 2      |
| Charlie | 85    | 3      |
| Dave    | 75    | 4      |
| Eve     | 70    | 5      |

In this example, even though Bob and Charlie have the same score, they are assigned unique row numbers 2 and 3, respectively.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

I recently worked on a project to assess the schema of a third-party vendor. Our organization has an internal support ticket tracking tool. The program used a SQL database, and after calculating its cost, we opted not to extend the contract. The objective was to create an in-house platform for managing internal support tickets.

My responsibility was to review the schema of the internal support database. We couldn't figure out what data was in which table because the structure was intricate and the table names were tough. Eventually, I was able to determine the relationship between tables and what data was contained in each. I've also given the columns proper names so that we can quickly identify what data is contained in which column. I used the sp_rename method to rename tables.

This article explains how to rename a column using the sp_rename stored procedure. I also demonstrated how to rename a column in SQL Server Management Studio. First, let us look at the fundamentals of renaming a column.

The Basics of Renaming Columns
Renaming a table's column is a simple task. We can use a system-stored process called sp_rename. Additionally, we can utilize SQL Server Management Studio to rename a column. The sp_rename stored procedure can rename the following:

• Database objects like tables, Stored procedures, and functions.
• Indexes and statistics
• User-defined datatypes.

In this article, we will learn how to rename any column of an SQL Server table using the sp_rename stored procedure.

How can you rename a column in SQL Server?
In SQL Server, we may rename any column or object using the sp_rename stored procedure. In this post, we'll look at how to rename columns using the sp_rename function.

The syntax for the sp_rename stored procedure is as follows.

Exec sp_rename 'original_schema_name.original_table_name.original_column_name', 'new_column_name' ,'object_type'

In the syntax

• original_schema_name.original_table_name.original_column_name: Specify the table name whose column you want to rename. If you are renaming a column of a table that exists in the user-defined schema, you must specify the table name in three three-part names.
• new_column_name: Specify the new name of the column.
• object_type: Specify the object type.

Let us understand the process with simple examples. Suppose you want to rename a column of the patient table. The original column name is Address, and we want to change it to patient_address. The sp_rename command to rename the column is as follows.

USE HospitalManagementSystem
GO
EXEC sys.sp_rename 'patients.address','patient_address','COLUMN'

Once the column is renamed, let us verify that the column has been renamed successfully. You can run the below query to view the columns of the patient table.
use HospitalManagementSystem
go
select table_name,column_name from information_schema.columns where table_name='Patients'

Output

As you can see in the above image, the column Address has been changed to patient_address.

Let us take another example. Suppose you want to rename the column of Sales. invoice table which exists in the Wideworldimportors database. The current name of the column is InvoiceDate, and the new name will be InvoiceCreateDate. The query to rename the column is the following.
EXEC sys.sp_rename 'Sales.Invoices.InvoiceDate','InvoiceCreateDate','COLUMN'

Here you can see, that we are changing the column name of the invoice table which is in the Sales schema. Therefore, we have used three-part naming. Once the column is renamed, execute the following T-SQL query to verify that the column has been renamed.
select table_name,column_name from information_schema.columns where table_name='Invoices'

Output

Renaming other objects in SQL Server
The sp_rename stored procedure can be used to rename other database objects, such as indexes, constraints, and stored procedures. The syntax of the sp_rename operation stays unchanged. The object_type argument for the sp_rename column will change. Let us consider a basic example.

Assume we wish to rename the index of the sales invoice table. The index's present name is 'IX_Sales_Invoices_ConfirmedDeliveryTime', which we would like to modify to 'IX_Sales_Invoices_ConfirmedDeliveryTime_New'. In the query, the object_type argument in the sp_rename method will be set to INDEX. The query to rename the index is shown below.

EXEC sys.sp_rename 'Sales.Invoices.IX_Sales_Invoices_ConfirmedDeliveryTime','IX_Sales_Invoices_ConfirmedDeliveryTime_New','INDEX'

Once the index is renamed, you can query sys. indexes dynamic management view to verify that the index has been renamed successfully. Note that whenever we rename any index, the statistics associated with the index will be renamed as well. Here is the query to verify both changes.
SELECT object_name(object_id)[TableName], name [IndexName], Type_desc [Index Type]
FROM sys.indexes where object_id=object_id('Sales.Invoices')

Output

Using SQL Server Management Studio to Rename
We can use SQL Server Management Studio to rename the database object. In the first section, we learnt how to rename columns and indexes using the sp_rename stored procedure.

In this example, we'll see how to rename a constraint in SQL Server Management Studio. For demonstration, I'll rename the constraint in the Sales.invoice table. The present constraint name is DF_Sales_Invoices_InvoiceID, which we will rename to Default_Sales_Invoices_InvoiceID. As the name implies, this constraint is a default constraint.

First, launch SQL Server Management Studio and connect to your database server. Expand databases. Expand the Wideworldimportors database.

A database contains many tables. Expand the Sales, Invoice, and Constraint tables. Press F2 or right-click on DF_Sales_Invoices_InvoiceID and choose Rename.

The name will be editable. Change the name to Default_Sales_Invoices_InvoiceID and hit enter. The name will be changed. The SQL Server management studio prompts a confirmation message that looks like the following image.

Click OK to change the name. Once changes are made, execute the following T-SQL query to verify that the constraint has been renamed successfully.
SELECT
  [constraint].name AS constraint_name,
  OBJECT_NAME([constraint].parent_object_id) AS table_name,
  [column].name AS column_name from
  sys.default_constraints [constraint]
JOIN
  sys.columns [column] ON [constraint].parent_object_id = [column].object_id
    AND [constraint].parent_column_id = [column].column_id
    where  OBJECT_NAME([constraint].parent_object_id)='Invoices'

Output

Let us take a look at some limitations and things to be considered before renaming any column.

Limitations and Considerations
If you are renaming any column in a table or renaming any object in a database, you must consider the following limitations and possible issues that might break the application.

• ALTER permission is needed on the object that you want to rename. Suppose you want to rename a column name; you must have ALTER object permission on the table whose column you are renaming.
• Renaming a column name always breaks the stored procedure or other objects (View, function, etc.) that are referencing that column. For example, you are renaming a column that is being used in a view. Therefore, make sure you modify all the stored procedures, functions, and triggers that reference the column that was renamed. You can use sys.sql_expression_dependencies to find all dependencies of the column.
• When you rename a stored procedure, the object's name in sys.sql_modules will not change. Hence Microsoft recommends dropping and recreating an object instead of just renaming it.
• When you rename a column of a table that is part of replication, the replication might break so if we want to rename the column of the replicated table, first, we must pause the replication, then rename the column using sp_rename or SQL Server management studio, update all database objects that are referencing the column, and finally, reinitialize replication with the new snapshot.

Conclusion
In this tutorial, we learned how to rename any column in a table. I demonstrated how to rename a column using a system-stored process called sp_rename, complete with syntax and easy examples. We also learned how to rename a column using SQL Server Management Studio. We can also use other tools, such as dbForge Studio for SQL Server, to run the stored procedure to rename a column. We also reviewed the limits and other difficulties that must be addressed before to renaming any object or column.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

One easy method to see if the given MySQL table has any rows is to run "SELECT 1 FROM TABLE". Rather than extracting any information from the table, it provides a result set that has a single column with the value 1 for each row that meets the requirements in the WHERE clause (if any).

Now let's look at an example.

First Step
I am going to make an Employee table.
CREATE TABLE Employee
(
employeeId INT IDENTITY(1,1) PRIMARY KEY,
employeeName VARCHAR(50)
)

Then Inserting Some values in that Employee table.
INSERT INTO Employee (employeeName)
VALUES('Johnson'),('Richard'), ('Willam'),('John')

After inserting the value, the result will be as follows:

Step 2
Execute the following Query without the WHERE Clause.
SELECT 1 FROM Employee

Output Message: (4 rows affected)

Result

Now Exceute with WHERE Clause.
SELECT 1 FROM Employee WHERE employeeName LIKE 'John%'

Output Message: (2 rows affected)

Result

I hope this blog has helped you to better grasp how to use SQL Server's SELECT 1 From the table function. Wishing you luck.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

Improving the effectiveness of queries is crucial for database optimization. Common Table Expressions (CTE) and temporary tables are two alternative ways that Microsoft SQL Server can improve query performance. But the question remains: which tactic works better in terms of efficiency and speed?

Frequently Used Table Terms (CTE)
Creating temporary result sets inside of a query is made easy and clear with the help of CTEs. By dividing intricate reasoning into smaller, easier-to-understand components, they improve query readability. CTEs can be referenced more than once and are defined within the query scope, which minimizes redundancy and enhances maintainability.

This is an example CTE query:
WITH CTE_Sales AS (
    SELECT ProductID, SUM(Quantity) AS TotalSales
    FROM Sales
    GROUP BY ProductID
)
SELECT P.ProductName, S.TotalSales
FROM Products P
JOIN CTE_Sales S ON P.ProductID = S.ProductID;

In this example, CTE_Sales calculates the total sales per product, which is later joined with the Products table.

Temporary Tables
Conversely, temporary tables are made physically inside the tempdb database. They hold temporary outcomes and continue until the session is over unless they are specifically removed. Although creating temporary tables requires more resource allocation and I/O cost, there are occasions where doing so improves speed, particularly for larger datasets or more complicated procedures.

Think about the following case with temporary tables:
CREATE TABLE #TempSales (
    ProductID INT,
    TotalSales INT
);

INSERT INTO #TempSales (ProductID, TotalSales)
SELECT ProductID, SUM(Quantity) AS TotalSales
FROM Sales
GROUP BY ProductID;

SELECT P.ProductName, TS.TotalSales
FROM Products P
JOIN #TempSales TS ON P.ProductID = TS.ProductID;

DROP TABLE #TempSales;

Here, #TempSales is created to hold the total sales per product before being joined with the Products table.

Comparing Speeds
Variations exist in the performance of CTEs and temporary tables due to variables such as query complexity, indexing, and dataset size. Because CTEs are in-memory and have minimal I/O cost, they often provide equivalent or occasionally higher performance in circumstances involving smaller datasets or simpler processes.

However, temporary tables may perform better than CTEs for larger datasets or more intricate tasks requiring several steps. Indexing can help temporary tables by enabling more efficient joins and quicker data retrieval.

In summary
Depending on a number of variables, one must decide whether to optimize query time using temporary tables or CTEs. Temporary tables excel at managing larger datasets and complex procedures, particularly when indexing and multi-step computations are involved. CTEs improve readability and are useful for simpler tasks.

In the end, experimenting, profiling, and taking into account the particular context of the query and database structure are frequently necessary for query performance optimization. Producing effective SQL queries in Microsoft SQL Server requires striking a balance between readability, maintainability, and efficiency.

Recall that there are two types of tools in the SQL optimization toolbox: temporary tables and CTEs. The best tool to use will depend on the particular needs of the task at hand.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

How to Acquire Every SQL An essential part of Microsoft SQL Server is SQL Server Agent, which lets database administrators utilize jobs to automate a variety of processes like data processing, backup, and maintenance. You can arrange for these jobs to execute at predetermined times, which will simplify the management and upkeep of your SQL Server system. This post will demonstrate how to use SQL Server Management Studio (SSMS) to get a list of all SQL Server Agent jobs.

Finding Jobs for SQL Server Agents
To obtain a list of every SQL Server Agent job in SSMS, take the following actions:

Step 1: Establish a connection with SQL ServerWorks with Server Agents?
To access your SQL Server instance, open SQL Server Management Studio.

Step 2: Launch the SQL Server Agent
Open SSMS and select the "Object Explorer" window from the menu on the left. The "SQL Server Agent" node can be seen by expanding the server node. Click "SQL Server Agent" with a right-click, then choose "Jobs."

Step 3. View Job List
Once you've selected "Jobs," the right-hand pane will display a list of all the SQL Server Agent jobs configured on the SQL Server instance. The list includes the following columns:
    Job ID
    Job Name
    Owner
    Enabled (whether the job is currently enabled or not)
    Last Run Date
    Next Run Date
    Last Run Outcome

You can see an overview of the jobs, their statuses, and when they were last run, making it easier to manage your SQL Server Agent jobs.

Step 4: Job Specifics
To see additional information about a particular job, right-click on the job name and choose "Properties." This will cause a new window to open, displaying a plethora of job-related information such as the timetable, steps, and notifications.
Using Query, Retrieve SQL Server Agent Jobs

The msdb database contains information on SQL Server Agent jobs. SQL queries can be used to obtain job details, their steps, and other pertinent data. The two main tables that we will be utilizing are sysjobs and sysjobsteps. This is an extensive SQL query that retrieves different information on SQL Server Agent jobs.

SELECT
    [sJOB].[job_id] AS [JobID]
    , [sJOB].[name] AS [JobName],
    case when d.name is null then 'No Schedule' else d.name end Schedule,
isnull (case d.freq_type
when '1 ' then 'Once'
when '4' then 'Daily'
when '8' then 'Weekly'
when '16' then 'Monthly'
when '32' then 'Monthly relative'
when '64' then 'When SQL Server Agent starts' end, 'None') as Frequency,
     CASE
        WHEN [sJOBH].[run_date] IS NULL OR [sJOBH].[run_time] IS NULL THEN NULL
        ELSE CAST(
                CAST([sJOBH].[run_date] AS CHAR(8))
                + ' '
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [LastRunDateTime]
    , CASE [sJOBH].[run_status]
        WHEN 0 THEN 'Failed'
        WHEN 1 THEN 'Succeeded'
        WHEN 2 THEN 'Retry'
        WHEN 3 THEN 'Canceled'
        WHEN 4 THEN 'Running' -- In Progress
      END AS [LastRunStatus]
    , STUFF(
            STUFF(RIGHT('000000' + CAST([sJOBH].[run_duration] AS VARCHAR(6)),  6)
                , 3, 0, ':')
            , 6, 0, ':')
        AS [LastRunDuration]
    , [sJOBH].[message] AS [LastRunStatusMessage]
    , CASE [sJOBSCH].[NextRunDate]
        WHEN 0 THEN NULL
        ELSE CAST(
                CAST([sJOBSCH].[NextRunDate] AS CHAR(8))
                + ' '
                + STUFF(
                    STUFF(RIGHT('000000' + CAST([sJOBSCH].[NextRunTime] AS VARCHAR(6)),  6)
                        , 3, 0, ':')
                    , 6, 0, ':')
                AS DATETIME)
      END AS [NextRunDateTime],
      isnull (convert (varchar,d.Date_Created), 'None') CreatedDate
FROM
    [msdb].[dbo].[sysjobs] AS [sJOB]

    LEFT JOIN (
                SELECT
                    [job_id]
                    , MIN([next_run_date]) AS [NextRunDate]
                    , MIN([next_run_time]) AS [NextRunTime]
                FROM [msdb].[dbo].[sysjobschedules]
                GROUP BY [job_id]
            ) AS [sJOBSCH]
        ON [sJOB].[job_id] = [sJOBSCH].[job_id]
    LEFT JOIN (
                SELECT
                    [job_id]
                    , [run_date]
                    , [run_time]
                    , [run_status]
                    , [run_duration]
                    , [message]
                    , ROW_NUMBER() OVER (
                                            PARTITION BY [job_id]
                                            ORDER BY [run_date] DESC, [run_time] DESC
                      ) AS RowNumber
                FROM [msdb].[dbo].[sysjobhistory]
                WHERE [step_id] = 0
            ) AS [sJOBH]
        ON [sJOB].[job_id] = [sJOBH].[job_id]
        AND [sJOBH].[RowNumber] = 1
        left outer join msdb.dbo.sysjobschedules e on e.job_id = [sJOB].job_id
        left outer join msdb.dbo.sysschedules d on e.schedule_id = d.schedule_id

The msdb.dbo.sysjobs table contains information about SQL Server Agent jobs.
The msdb.dbo.sysjobsteps table contains information about the steps within those jobs.
We use the inner join clause to link the two tables based on the job_id column.

In summary
Monitoring and controlling automated processes in a SQL Server environment requires retrieving information about SQL Server Agent jobs. With the SQL query that is provided, you may get comprehensive details on jobs and the steps that go along with them. Database administrators can maintain the functionality and health of their SQL Server instances by routinely accessing and evaluating this data. Furthermore, by utilizing this data, job-related problems can be resolved and work schedules can be optimized for increased productivity.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.