Have you forgotten your password for the system administrator (SA) account in SQL Server? Do you want to reset SA password in SQL Server? If you are unable to recall it, Don’t panic! Here in this article, we are providing you with free methods to reset the System Administrator (SA) password in SQL Server.

Use Single User Mode
Make sure the Microsoft SQL Server Management System must be installed on your system before using the Single-User mode. Given steps can be followed,

Enter Windows key+R in the administrator mode.

Type the command net stop MSSQLSERVER and then click the Enter button to stop the SQL instance from running.

Now, restart the SQL Server in the Single-User mode by using this command: net start MSSQLSERVER /m”SQLCMD” and then hit the Enter button.

Here, connect to the SQL Server by entering the command sqlcmd and click on Enter button.

Create user credentials (user name and password) using T-SQL commands. CREATE LOGIN name WITH PASSWORD=’password’. Here, “name” specifies the account name and “password” specifies the new password.

Now, type SP_ADDSRVROLEMEMBER name,‘SYSADMIN’ command to add this recently created user to the System Administrator role using T-SQL command.

Here, you need to exit the SQLCMD command line by typing the command exit and clicking on the Enter button.

Now open Microsoft SQL Server Management Studio using the SQL Server Authentication and login with the recently created user by providing the user name and password and click on Connect button.

Here in the Object Explore section, expand the Security tab>Login and right-click on the SA and click on Properties.

Now in the Login Properties windows, provide the new credentials and click on OK and close it.
This is how you can easily reset SA password in SQL Server effortlessly. I hope this helps!

HostForLIFEASP.NET SQL Server 2019 Hosting

In this article, we will cover how to bring a database online from the suspect mode in an SQL server. Along with that, we also explain the meaning of SQL database in suspect mode and the reasons for this issue. So, let us start the article.

First, let us understand what SQL suspect mode means.

When the SQL database shows the suspect mode instead of online, it resembles that the database has started to recover the corrupted file but is not finished yet. There are various reasons for this issue. Below we suggested a few common reasons for the error message.

Why Does This Error Occur?
The SQL database goes to suspect mode due to many reasons. However, some reasons are more prevalent than others. Therefore, before you bring the database online from the suspect mode in the SQL server, we discuss some common causes for the SQL server issue.

    Abrupt shutdown of the SQL server leads to various issues. SQL Suspect mode is also one of those problems.
    When the database is unable to access the location of the log files and other important files, it shows the SQL server in suspect mode.
    If the files required by the database are opened in any other third-party application or by antivirus software, you will also experience the same issue.
    Insufficient disk space is another main reason for SQL databases being in suspect mode.
    Server crash sent your SQL database in Suspect mode.
    Sometimes, the SQL server database file corruption also causes this issue.

The above are a few scenarios when your SQL database is in suspect mode. We will explain an effective method to fix the problem.
How to Bring Database Online from Suspect Mode in SQL Server?

You have understood what are the main reasons for your database to be in suspect mode. Now, it is time to learn how to recover SQL database from suspect mode. We discuss the most efficient and easy solution to fix the DBMS issue. To perform the steps you need the SSMS (SQL Server Management Studio).

Perform the below steps to repair database in suspect mode in SQL Server

First, launch the SSMS and connect it to the database.

Now, choose New Query and set the database to the emergency mode by passing the following command.
EXEC sp_resetstatus ‘db_name’;

ALTER DATABASE db_name SET EMERGENCY

After that, give the below command for Consistency Check. It helps you to identify whether the database files are corrupted or not.
DBCC CHECKDB (‘database_name’)

Enable Single-User Mode and execute the below command to roll back the last transaction.
ALTER DATABASE database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Take the backup of your database items to prevent data loss. give the following command to repair and rebuild the SQL database lost missing rows.
DBCC CHECKDB (‘database_name’, REPAIR_ALLOW_DATA_LOSS)

After that, deactivate the Single-User mode and enable the Multi-User Mode.
ALTER DATABASE database_name SET MULTI_USER

After performing the above steps, anyone can easily restore their corrupted database files and repair database in suspect mode in SQL server.

I hope after reading this article you will get a satisfactory solution to recover SQL database from suspect mode. We explained what are the main reasons behind the database issue and also explained a simple yet effective manual method. However, if the problem originates due to corrupted database files, we suggest you opt for any advanced professional approach.

HostForLIFEASP.NET SQL Server 2019 Hosting

Don’t waste your time in anger, regrets, worries, and grudges. Life is too short to be unhappy. One of the most popular and important questions that people often ask in complex database performance checks is how to measure the execution time of a stored procedure when it contains many statements. Well, honestly, the solution is very straightforward. Today we will discuss the execution time of stored procedures.

It is very easy to measure the execution time of a stored procedure. It is not necessary to add up the entire execution time of the stored procedure. Before running the stored procedure, just run the following command and you will see the last line in the message section with the time required to run the execution plan for the stored procedures.

SET STATISTICS TIME ON
EXEC YourSPName

The output will look like this.
SQL Server Execution Times: CPU time = 450 ms, Elapsed time = 1150 ms.
SQL Server Execution Times: CPU Time = 50 ms, Elapsed Time = 100 ms.
SQL Server Execution Times: CPU time = 1450 ms, Elapsed time = 1950 ms.
SQL Server Execution Times: CPU time = 2150 ms, Elapsed time = 3250 ms.

If your stored procedure has three statements, the first three represent the execution time of a single query. However, the last line represents the addition or commutative time for all the query statements together.

I hope you like this.

Thanks.

HostForLIFEASP.NET SQL Server 2019 Hosting

In this tutorial, I will tell you about How to search a string entire database on SQL Server.

--USE DATABASE_NAME
DECLARE @SearchStr nvarchar(100) = 'Class VIII'
DECLARE @Results TABLE (ColumnName nvarchar(370), ColumnValue nvarchar(3630))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256), @ColumnName nvarchar(128), @SearchStr2 nvarchar(110)
SET  @TableName = ''
SET @SearchStr2 = QUOTENAME('%' + @SearchStr + '%','''')

WHILE @TableName IS NOT NULL

BEGIN
    SET @ColumnName = ''
    SET @TableName =
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE         TABLE_TYPE = 'BASE TABLE'
            AND    QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND    OBJECTPROPERTY(
                    OBJECT_ID(
                        QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)
                         ), 'IsMSShipped'
                           ) = 0
    )

    WHILE (@TableName IS NOT NULL) AND (@ColumnName IS NOT NULL)

    BEGIN
        SET @ColumnName =
        (
            SELECT MIN(QUOTENAME(COLUMN_NAME))
            FROM     INFORMATION_SCHEMA.COLUMNS
            WHERE         TABLE_SCHEMA    = PARSENAME(@TableName, 2)
                AND    TABLE_NAME    = PARSENAME(@TableName, 1)
                AND    DATA_TYPE IN ('char', 'varchar', 'nchar', 'nvarchar', 'int', 'decimal')
                AND    QUOTENAME(COLUMN_NAME) > @ColumnName
        )

        IF @ColumnName IS NOT NULL

        BEGIN
            INSERT INTO @Results
            EXEC
            (
                'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)
                FROM ' + @TableName + ' (NOLOCK) ' +
                ' WHERE ' + @ColumnName + ' LIKE ' + @SearchStr2
            )
        END
    END
END

For example, I will search the above mentioned string then the sample output is as follows:

HostForLIFEASP.NET SQL Server 2019 Hosting

ANSI_NULLS define the comparison rule for NULL values in SQL Server.
 
When SET ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. A SELECT statement that uses WHERE column_name = NULL returns the rows that have null values in column_name.
 
Example

But when SET ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name.
 
Example

HostForLIFEASP.NET SQL Server 2019 Hosting

Introduction
Hope you're familiar with the concepts of what's a primary key in a relational database management system like SQL Server. But, you need more details to better understand it, or you need some review or refresher for yourself, you have come to the right place.

This article will discuss a primary key, entity integrity, primary key constraint, composite keys, and the benefits of a primary key.

Let's get started.

What's A Primary Key?
A primary key is a logical concept where a column or field uniquely identifies each record inside a database table. Moreover, it has multiple attributes: it must not be null, and it must be unique.

Easy to remember, right? But if you're curious, we'll test and see what will happen when we try to pass a NULL and duplicate value in a primary column in the latter part of the article.
What's Entity Integrity?

Entity integrity is a rule for practical database construction, and this practice is widely used. It is a process of enforcing the primary key for each table in a database.

Therefore, it is implemented to uniquely identify each row inside the table by specifying a primary key inside our database table.
What's a Primary Key Constraint?

A primary key constraint is a restriction that basically ensures entity integrity.

Note: Unique constraint also ensures entity integrity.
Benefits of Primary Key and Entity Integrity

Proper usage and selection of a primary key and maintaining entity integrity prevent duplicate records and other issues that would indirectly compromise the integrity of the database.

Things to Remember About Primary Keys
    A primary key column cannot have NULL values.
    A primary key column cannot have duplicate values.
    A table can have only one primary key constraint.
    When multiple columns are used as primary keys, they are called composite keys.
    It is good to remember that a primary key is the default clustered index if a clustered index on the table does not exist.

Composite Keys
Now, you might ask why I see multiple primary keys in one table? Those are called composite keys.

Composite keys use two or more fields from a table to create a unique value.

Therefore, it guarantees uniqueness only when combined columns, but they don't guarantee uniqueness individually.

Creating A Primary Key on A Table
Let's show how we can define a column as a primary key.
CREATE TABLE EMPLOYEE(
[Id] int NOT NULL IDENTITY(1,1),
[FirstName] nvarchar(50),
[LastName] nvarchar(50),
[Age] int,
PRIMARY KEY (Id))

In the code sample above, we have seen that using the PRIMARY KEY then passing the column Id, we have defined the primary key of the EMPLOYEE table.

Let's try to see the result below.

Add a Primary Key on A Table
Let's recreate the table from the previous example, but we will not create the primary key.
We will create a primary key after we have created the table.
Let's try to see the code sample below.
IF EXISTS (SELECT * FROM  [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] ='EMPLOYEE')
BEGIN
    DROP TABLE EMPLOYEE;
END
GO
BEGIN
CREATE TABLE EMPLOYEE(
    [Id] int NOT NULL IDENTITY(1,1),
    [FirstName] nvarchar(50),
    [LastName] nvarchar(50),
    [Age] int);
END
GO
BEGIN
ALTER TABLE EMPLOYEE ADD PRIMARY KEY(Id);
END

Just a note, if you have removed the IDENTITY (1,1), everything will still be good and have no errors because it's an auto-incrementing column.

Although these two are used together, there's no requirement when defining a primary key column that it needs to be an identity column.

Let's try to see an example below.
IF EXISTS (SELECT * FROM  [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] ='EMPLOYEE')
BEGIN
    DROP TABLE EMPLOYEE;
END
GO
BEGIN
CREATE TABLE EMPLOYEE(
    [Id] int NOT NULL,
    [FirstName] nvarchar(50),
    [LastName] nvarchar(50),
    [Age] int);
END
GO
BEGIN
ALTER TABLE EMPLOYEE ADD PRIMARY KEY(Id);
END

Again, another note, if we have forgotten the NOT NULL that makes the Id column nullable, it will give you an error.

You'll probably see an error like this
    "Cannot define a PRIMARY KEY constraint on nullable column in table 'EMPLOYEE.'"

Let's try to see an example below.
IF EXISTS (SELECT * FROM  [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] ='EMPLOYEE')
BEGIN
    DROP TABLE EMPLOYEE;
END
GO
BEGIN
CREATE TABLE EMPLOYEE(
    [Id] int, -- NOT NULL REMOVED FOR YOU TO SEE THE ERROR MESSAGE
    [FirstName] nvarchar(50),
    [LastName] nvarchar(50),
    [Age] int);
END
GO
BEGIN
ALTER TABLE EMPLOYEE ADD PRIMARY KEY(Id);
END

Let's try to see the result below.

Delete Primary Key on A Table
In this section, let's try to recreate the table again, but after creating the table, let's make a primary key with the name of [PK_ON_EMPLOYEE_TABLE].
The reason for giving the primary key a custom name is so we won't have a hard time knowing its name when we need to drop the primary key.

Let's try to see the example below.

PRINT 'STEP 0. DROP EMPLOYEE TABLE IF EXISTS'

IF EXISTS (SELECT * FROM  [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] ='EMPLOYEE')
BEGIN
    PRINT 'STEP 0.1 DROPPING EMPLOYEE TABLE'
    DROP TABLE EMPLOYEE;
END
GO

PRINT 'STEP 1. CREATE THE TABLE'

BEGIN
CREATE TABLE EMPLOYEE(
    [Id] int NOT NULL IDENTITY(1,1),
    [FirstName] nvarchar(50),
    [LastName] nvarchar(50),
    [Age] int);
PRINT 'STEP 1.1 EMPLOYEE TABLE CREATED'
END
GO

BEGIN
PRINT 'STEP 2. EMPLOYEE TABLE ADDING PRIMARY KEY [PK_ON_EMPLOYEE_TABLE]'
ALTER TABLE EMPLOYEE ADD CONSTRAINT [PK_ON_EMPLOYEE_TABLE] PRIMARY KEY(Id);
END
GO

BEGIN
PRINT 'STEP 3. EMPLOYEE TABLE REMOVING THE PRIMARY KEY'
ALTER TABLE EMPLOYEE
DROP CONSTRAINT [PK_ON_EMPLOYEE_TABLE];
END

Inserting NULL values into Primary Key Column
This obviously will show an error because we're violating the primary key constraint.
Still, we'll see how the SQL Server will react when inserting NULL values out of curiosity.

Let's try to see an example below.
IF EXISTS (SELECT * FROM  [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] ='EMPLOYEE')
BEGIN
    DROP TABLE EMPLOYEE;
END
GO
BEGIN

CREATE TABLE EMPLOYEE(
    [Id] int NOT NULL,
    [FirstName] nvarchar(50),
    [LastName] nvarchar(50),
    [Age] int);
END
GO

BEGIN
ALTER TABLE EMPLOYEE ADD PRIMARY KEY(Id) ;
END
GO

BEGIN
--LET'S INSERT NULL value and expect an error
INSERT INTO [dbo].[EMPLOYEE] ([Id],[FirstName],[LastName],[Age])
VALUES
(NULL, 'Jin', 'Necesario', 100)
END
GO

Let's try to see the result below.

Inserting Duplicate Values into Primary Key Column
Again, this will obviously show an error because we're violating the primary key constraint. Still, we'll see how the SQL Server will react when inserting duplicate values out of curiosity.

Let's try to see an example below.
IF EXISTS (SELECT * FROM  [INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] ='EMPLOYEE')
BEGIN
    DROP TABLE EMPLOYEE;
END
GO
BEGIN

CREATE TABLE EMPLOYEE(
    [Id] int NOT NULL,
    [FirstName] nvarchar(50),
    [LastName] nvarchar(50),
    [Age] int);
END
GO

BEGIN
ALTER TABLE EMPLOYEE ADD PRIMARY KEY(Id) ;
END
GO

BEGIN
--LET'S INSERT SAME Id value of 1 and expect an error
INSERT INTO [dbo].[EMPLOYEE] ([Id],[FirstName],[LastName],[Age])
VALUES
(1, 'Jin', 'Necesario', 100),
(2, 'Vincent','Necesario', 100),
(1, 'Jin Vincent','Necesario', 100)
END
GO

Let's try to see the result below.

HostForLIFEASP.NET SQL Server 2019 Hosting

NDepend is a static code analyzing tool which will help us to improve the source code quality by reducing the issues in our code. Patrick Smacchia introduced NDepend in 2007. This tool will likely find hundreds or even thousands of issues affecting our code base. Stopping all work to try and fix all issues for weeks would be quite unproductive. This is the only tool that proposes to focus on progress from a baseline. The tool estimates the technical debt evolution since the baseline.

Recent code smells that should be fixed before committing any code to source control are highlighted in the code just edited. NDepend is the only .NET tool that can tell the developer that over the past hour, the code just written has introduced debt that would cost for example about 30 minutes should it have to be repaid later. Knowing this, the developer can fix the code before even committing it to the source control.

In this post, we can see all the steps to download and install NDepend in our Windows machine and analyze our source code.  

Please refer to NDepend official site for more information.  

This tool can be useful for developers, architects, and DevOps people. This is integrated with all Visual Studio versions from 2010 to 2022. All features are available side-by-side with Visual Studio Code and Rider thanks to the standalone app VisualNDepend.exe

We can use this tool to analyze .NET 6.0, .NET 5.0, (and soon .NET 7), .NET Core and other versions, .NET Fx 4.x, ASP.NET Core, Blazor, Xamarin, Unity and UWP applications.
Download and Install NDepend on Windows machine.

NDepend is giving a 14-day fully functional trial version. We can use this trial version to explore all the features and once we are satisfied, we can buy the professional version.

We can extract the Zip file in any folder except '%ProgramFiles%\NDepend'. This might provoke problems because of Windows protection.

The Zip file includes four main executable files. 
NDepend.Console.exe is a console application which is used for registering and unregistering the license of NDepend.  It can also be used from your CI/CD to generate HTML+js reports about the health of your code base (see some sample reports here)

NDepend.PowerTools.exe is another console application which will help us to perform various operations via command line. Notice that power-tools are Open-Source and relies on the NDepend.API.

Currently, this tool has 18 various usages. We can choose any of these choices and hit enter key to continue.

I have chosen ‘r’.  This power tool analyzed the .NET 5.0 and 6.0 public APIs.  

NDepend.VisualStudioExtension.Installer.exe is an installer file. This file will install the NDepend extension in our Visual Studio version.

I am using Visual Studio 2022 on my machine. We can choose our respective versions.

You must agree to the license terms. It may take some time to complete the entire extension installation.
After successful installation, we can open Visual Studio. 

We can see the new extension under the Extensions menu. We can choose the New Project option from the menu.

We can give a valid Project Name. We are going to analyze one .NET 6.0 project. I have already written one project for JWT Authentication. We can use this project to analyze with NDepend.

We can click Add VS Solution or Project button and add our existing .NET 6.0 project. 

We can choose our existing project by clicking on the Browse button. We can also select multiple projects by clicking on the Check All button. It will select all the compatible projects that exist in our system. 

We can click the play button on the left top corner to start the analysis.  
After analysis (it just takes a few seconds), we can see new menus in the NDepend toolbar. 

We can click Dashboard option. 

We can see the elaborated analysis in the dashboard page. Total 355 lines in our project. Among the 355 lines, 24 lines are comments. We got an exceptionally good A rating. Still there are 5 rules violation and 15 issues in the code. 

We can click on 5 violated rules and see the details below.

Same way, we can click on the single issue with priority High and see more details of the issue.
Dependency Graph is another option in the tool. We can choose this option from the menu. See a 6 minutes intro video about the graph on youtube here.

Dependency Matrix is another good option in this tool. See a 5 minutes intro video about the matrix on youtube here.

Code Metrics View is also one of the good features in this tool. See a 4 minutes intro video about this metric view on youtube here.

VisualNDepend.exe is a standalone tool which can be used to analyze the .NET projects without Visual Studio.

We can create a new project or open an existing NDepend project and analyze using this Visual tool.

All the features like Dashboard, Dependency Graph, Dependency Matrix, and Code Matrics View are available in this tool as well.

Once we have bought the professional license, we can update the license using  NDepend Console tool.  

Conclusion
In this post, we have seen the introduction of NDepend tool which is a static code analysis tool that can be used for improving the code quality of our .NET or .NET Core source code. We have installed the NDepend extension in Visual Studio 2022 and analyzed one existing .NET 6.0 project using this tool. We have seen various features like Dashboard, Dependency Graph, Dependency Matrix, and Code Matrics View in this tool. I have just given an introduction about NDepend tool in this post. We can see more information about this tool in an upcoming post later. Please feel free to give your valuable feedback about this article.

HostForLIFEASP.NET SQL Server 2019 Hosting

We get a procedure list with the help of DMV like " Sys.objects & sys.parameters"

USE [SqlBank]

SELECT s.NAME StoreProcedure, p.NAME Parameter FROM SYS.PARAMETERS p
JOIN SYS.OBJECTS s on p.object_id=s.object_id
WHERE TYPE='P'
ORDER BY p.NAME

From the above query, we get Procedure name with parameters

Output

Thank you for reading the blog.

HostForLIFEASP.NET SQL Server 2019 Hosting

To compare SQL DB objects we use Dynamic views Like  "SYS.SYSOBJECTS, "SYS.SYSCOMMENTS", "INFORMATION_SCHEMA.COLUMNS"

STEP 1
Declare local varible
DECLARE @SourceDbName SYSNAME = 'SqlBank'; -- Source Database Name
DECLARE @TargetDbName SYSNAME = 'SqlBankCore';-- Target Databse Name

DECLARE @SYSobjectCommonQuery NVARCHAR(4000);
DECLARE @SYSCommentsQuery NVARCHAR(4000);

DECLARE @SourceSYSObjectsExecute NVARCHAR(4000);
DECLARE @SourceSYSCommentExecute NVARCHAR(4000);

DECLARE @TargetSYSObjectsExecute NVARCHAR(4000);
DECLARE @TargetSYSCommentExecute NVARCHAR(4000);

DECLARE @InformationCommonQuery NVARCHAR(4000);
DECLARE @ExecuteSourceInformationColumn NVARCHAR(4000);

STEP 2
Declare Variable table for Objects xtypes
DECLARE @Tbl_Xtypes TABLE(xtype CHAR(2),xtypeDefination VARCHAR(1000))

INSERT INTO @Tbl_Xtypes Values
('AF','Aggregate function (CLR)'),('C ','CHECK Constraint'),
('D','Default or DEFAULT Constraint'),('F','FOREIGN KEY Constraint'),
('FN','Scalar Function'),('FS','Assembly (CLR) Scalar-Function'),
('FT','Assembly (CLR) Table-Valued Function'),('IF','In-lined Table Function'),
('IT','Internal Table'),('L','Log'),('P','Stored Procedure'),
('PC','Assembly (CLR) Stored Procedure'),
('PK','PRIMARY KEY Constraint (Type is K)'),
('RF','Replication Filter Stored Procedure'),('S','System Table'),
('SN','Synonym'),('SQ','Service Queue'),
('TA','Assembly (CLR) DML Trigger'),('TF','Table Function'),
('TR','SQL DML Trigger'),('TT','Table Type'),
('U','User Table'),('UQ','UNIQUE Constraint'),
('V','View') ,('X','Extended Stored Procedure')

STEP 3
Assign Common Query for local object
SET @SYSobjectCommonQuery=N'
SELECT NAME,ID,XTYPE,UID,INFO,STATUS,BASE_SCHEMA_VER,REPLINFO,PARENT_OBJ
,CRDATE,FTCATID,SCHEMA_VER,STATS_SCHEMA_VER,TYPE,USERSTAT,SYSSTAT,INDEXDEL,
REFDATE,VERSION,DELTRIG,INSTRIG,UPDTRIG,SELTRIG,CATEGORY,CACHE
FROM SYS.SYSOBJECTS WHERE XTYPE NOT IN (''S'') ORDER BY NAME ASC';

SET @SYSCommentsQuery =N'SELECT id,number,colid,status,ctext,texttype,
language,encrypted,compressed,text FROM sys.syscomments';

STEP 4
SET Source & Target Database Common query to get DB objects

SET @SourceSYSObjectsExecute=N'USE ['+@SourceDbName+'] '+@SYSobjectCommonQuery+'';
SET @SourceSYSCommentExecute=N'USE ['+@SourceDbName+'] '+@SYSCommentsQuery+'';
SET @TargetSYSObjectsExecute=N'USE ['+@TargetDbName+'] '+@SYSobjectCommonQuery+'';
SET @TargetSYSCommentExecute=N'USE ['+@TargetDbName+'] '+@SYSCommentsQuery+'';

STEP 5
Create Temp Table to insert SYS.SYSObjects Objects to Temp Table

IF(OBJECT_ID('tempdb..#Tbl_SourceObjects') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_SourceObjects
END

CREATE TABLE #Tbl_SourceObjects(name sysname,id int,xtype char(2),uid smallint,
info smallint,status int,base_schema_ver int,replinfo int,parent_obj int,
crdate datetime,ftcatid smallint,schema_ver int,stats_schema_ver int,type char(2),
userstat smallint,sysstat smallint,indexdel smallint,refdate datetime,version int,
deltrig int,instrig int,updtrig int,seltrig int,category int,cache smallint)

INSERT INTO #Tbl_SourceObjects
EXEC sp_executesql @SourceSYSObjectsExecute

STEP 6
Create Temp Table to insert SYS.SYSComments Objects to Temp Table

IF(OBJECT_ID('tempdb..#Tbl_SourceComments') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_SourceComments
END

CREATE TABLE #Tbl_SourceComments(id int,number smallint,colid smallint,
status smallint,ctext varbinary(8000),texttype smallint,language smallint,
encrypted bit,compressed bit, text nvarchar(4000))

INSERT INTO #Tbl_SourceComments
EXEC sp_executesql @SourceSYSCommentExecute

STEP 7
IF(OBJECT_ID('tempdb..#Tbl_TargetObjects') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_TargetObjects
END

CREATE TABLE #Tbl_TargetObjects(name sysname,id int,xtype char(2),uid smallint,
info smallint,status int,base_schema_ver int,replinfo int,parent_obj int,
crdate datetime,ftcatid smallint,schema_ver int,stats_schema_ver int,type char(2),
userstat smallint,sysstat smallint,indexdel smallint,refdate datetime,version int,
deltrig int,instrig int,updtrig int,seltrig int,category int,cache smallint)

INSERT INTO #Tbl_TargetObjects
EXEC sp_executesql @TargetSYSObjectsExecute

IF(OBJECT_ID('tempdb..#Tbl_TargetComments') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_TargetComments
END

CREATE TABLE #Tbl_TargetComments(id int,number smallint,colid smallint,
status smallint,ctext varbinary(8000),texttype smallint,language smallint,
encrypted bit,compressed bit, text nvarchar(4000))

--print @SourceSYSCommentExecute
INSERT INTO #Tbl_TargetComments
EXEC sp_executesql @TargetSYSCommentExecute

--======================================================================

Select @SourceDbName [Source DataBase Name], Main.name [Source Object Name],
@TargetDbName[Target DataBase Name],Main.[Object Defination],SubMain.name [Target Object Name]
,Main.text[Source Object text],SubMain.text [Target Object text],
CASE WHEN Main.text=SubMain.text Then
 Concat(Main.[Object Defination], '  Object Match with Source Databse')
 else
 Concat(Main.[Object Defination], ' Object Mismatch Or Not Found with Source Database')
 end [Global Message]
from
(
SELECT o.name, cs.id,cs.number,cs.colid,cs.status,cs.ctext,cs.texttype,
cs.language,cs.encrypted,cs.compressed,ISNULL(cs.text,'') text,
CASE WHEN o.xtype in (SELECT x.xtype from @Tbl_Xtypes x)
THEN (Select xx.xtypeDefination from @Tbl_Xtypes xx where xx.xtype=o.xtype)
else '' end [Object Defination]
FROM #Tbl_SourceComments cs join
#Tbl_SourceObjects o on o.id=cs.id
) Main
left join
(
select css.id,oo.name,ISNULL(css.text,'') text,oo.xtype FROM
#Tbl_TargetComments css  left join
#Tbl_TargetObjects oo on css.id=oo.id

)SubMain  on Main.name=SubMain.name

SET @InformationCommonQuery = N'
select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION
,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_PRECISION_RADIX
,NUMERIC_SCALE,DATETIME_PRECISION,ISNULL(CHARACTER_SET_CATALOG,'''')
CHARACTER_SET_CATALOG
,ISNULL(CHARACTER_SET_SCHEMA,'''') CHARACTER_SET_SCHEMA,
ISNULL(CHARACTER_SET_NAME,'''')
CHARACTER_SET_NAME,
ISNULL(COLLATION_CATALOG,'''') COLLATION_CATALOG
,ISNULL(COLLATION_SCHEMA,'''') COLLATION_SCHEMA,ISNULL(COLLATION_NAME,'''')
COLLATION_NAME,ISNULL(DOMAIN_CATALOG,'''') DOMAIN_CATALOG,ISNULL(DOMAIN_SCHEMA,'''')
DOMAIN_SCHEMA
,ISNULL(DOMAIN_NAME,'''') DOMAIN_NAME from INFORMATION_SCHEMA.COLUMNS';

SET @ExecuteSourceInformationColumn = N'USE ['+@SourceDbName+'] '+@InformationCommonQuery+'';
IF(OBJECT_ID('tempdb..#Tbl_SourceInfoTable') Is not null)
BEGIN
DROP TABLE #Tbl_SourceInfoTable
END

CREATE TABLE #Tbl_SourceInfoTable(TABLE_CATALOG nvarchar(256),TABLE_SCHEMA nvarchar(256),
TABLE_NAME sysname,COLUMN_NAME sysname,ORDINAL_POSITION int,
COLUMN_DEFAULT nvarchar(4000),IS_NULLABLE varchar(3),DATA_TYPE nvarchar(256),
CHARACTER_MAXIMUM_LENGTH int,CHARACTER_OCTET_LENGTH int,NUMERIC_PRECISION tinyint,
NUMERIC_PRECISION_RADIX smallint,NUMERIC_SCALE int,DATETIME_PRECISION int,
CHARACTER_SET_CATALOG sysname ,CHARACTER_SET_SCHEMA sysname,
CHARACTER_SET_NAME sysname,COLLATION_CATALOG sysname,COLLATION_SCHEMA sysname,
COLLATION_NAME sysname,DOMAIN_CATALOG sysname,DOMAIN_SCHEMA sysname,
DOMAIN_NAME sysname)

INSERT INTO #Tbl_SourceInfoTable
exec sp_executesql @ExecuteSourceInformationColumn

DECLARE @ExecuteTargetInformationColumn NVARCHAR(4000);

SET @ExecuteTargetInformationColumn = N'USE ['+@TargetDbName+'] '+@InformationCommonQuery+'';
IF(OBJECT_ID('tempdb..#Tbl_TargetInfoTable') Is not null)
BEGIN
DROP TABLE #Tbl_TargetInfoTable
END

CREATE TABLE #Tbl_TargetInfoTable(TABLE_CATALOG nvarchar(256),TABLE_SCHEMA nvarchar(256),
TABLE_NAME sysname,COLUMN_NAME sysname,ORDINAL_POSITION int,
COLUMN_DEFAULT nvarchar(4000),IS_NULLABLE varchar(3),DATA_TYPE nvarchar(256),
CHARACTER_MAXIMUM_LENGTH int,CHARACTER_OCTET_LENGTH int,NUMERIC_PRECISION tinyint,
NUMERIC_PRECISION_RADIX smallint,NUMERIC_SCALE int,DATETIME_PRECISION int,
CHARACTER_SET_CATALOG sysname ,CHARACTER_SET_SCHEMA sysname,
CHARACTER_SET_NAME sysname,COLLATION_CATALOG sysname,COLLATION_SCHEMA sysname,
COLLATION_NAME sysname,DOMAIN_CATALOG sysname,DOMAIN_SCHEMA sysname,
DOMAIN_NAME sysname)

INSERT INTO #Tbl_TargetInfoTable
EXEC sp_executesql @ExecuteTargetInformationColumn

SELECT s.TABLE_NAME [Source Table Name],s.COLUMN_NAME [Source Col Name],
s.DATA_TYPE [Source Col Data Type],
IC.TABLE_NAME [Target Table Name],
IC.COLUMN_NAME [Target Col Name],IC.DATA_TYPE [Target Col Data Type] ,
CASE WHEN s.COLUMN_NAME= IC.COLUMN_NAME
THEN 'Match'
ELse 'Column Mismatch Or Not Found'
end [Global Message Info]
FROM #Tbl_SourceInfoTable s LEFT JOIN
     #Tbl_TargetInfoTable IC
              ON s.COLUMN_NAME=IC.COLUMN_NAME
              AND s.TABLE_NAME=IC.TABLE_NAME

Combine Script
DECLARE @SourceDbName SYSNAME = 'SqlBank';
DECLARE @TargetDbName SYSNAME = 'SqlBankCore';

DECLARE @SYSobjectCommonQuery NVARCHAR(4000);
DECLARE @SYSCommentsQuery NVARCHAR(4000);

DECLARE @SourceSYSObjectsExecute NVARCHAR(4000);
DECLARE @SourceSYSCommentExecute NVARCHAR(4000);

DECLARE @TargetSYSObjectsExecute NVARCHAR(4000);
DECLARE @TargetSYSCommentExecute NVARCHAR(4000);

DECLARE @InformationCommonQuery NVARCHAR(4000);
DECLARE @ExecuteSourceInformationColumn NVARCHAR(4000);

--=======Below variable table for xtype & xtype defination list--=====
DECLARE @Tbl_Xtypes TABLE(xtype CHAR(2),xtypeDefination VARCHAR(1000))

INSERT INTO @Tbl_Xtypes Values
('AF','Aggregate function (CLR)'),('C ','CHECK Constraint'),
('D','Default or DEFAULT Constraint'),('F','FOREIGN KEY Constraint'),
('FN','Scalar Function'),('FS','Assembly (CLR) Scalar-Function'),
('FT','Assembly (CLR) Table-Valued Function'),('IF','In-lined Table Function'),
('IT','Internal Table'),('L','Log'),('P','Stored Procedure'),
('PC','Assembly (CLR) Stored Procedure'),
('PK','PRIMARY KEY Constraint (Type is K)'),
('RF','Replication Filter Stored Procedure'),('S','System Table'),
('SN','Synonym'),('SQ','Service Queue'),
('TA','Assembly (CLR) DML Trigger'),('TF','Table Function'),
('TR','SQL DML Trigger'),('TT','Table Type'),
('U','User Table'),('UQ','UNIQUE Constraint'),
('V','View') ,('X','Extended Stored Procedure')

SET @SYSobjectCommonQuery=N'
SELECT NAME,ID,XTYPE,UID,INFO,STATUS,BASE_SCHEMA_VER,REPLINFO,PARENT_OBJ
,CRDATE,FTCATID,SCHEMA_VER,STATS_SCHEMA_VER,TYPE,USERSTAT,SYSSTAT,INDEXDEL,
REFDATE,VERSION,DELTRIG,INSTRIG,UPDTRIG,SELTRIG,CATEGORY,CACHE
FROM SYS.SYSOBJECTS WHERE XTYPE NOT IN (''S'') ORDER BY NAME ASC';

SET @SYSCommentsQuery =N'SELECT id,number,colid,status,ctext,texttype,
language,encrypted,compressed,text FROM sys.syscomments';

--=============Common_Query_For_Both_Database--=====================
SET @SourceSYSObjectsExecute=N'USE ['+@SourceDbName+'] '+@SYSobjectCommonQuery+'';
SET @SourceSYSCommentExecute=N'USE ['+@SourceDbName+'] '+@SYSCommentsQuery+'';
SET @TargetSYSObjectsExecute=N'USE ['+@TargetDbName+'] '+@SYSobjectCommonQuery+'';
SET @TargetSYSCommentExecute=N'USE ['+@TargetDbName+'] '+@SYSCommentsQuery+'';

--PRINT @TargetSYSCommentExecute

IF(OBJECT_ID('tempdb..#Tbl_SourceObjects') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_SourceObjects
END

CREATE TABLE #Tbl_SourceObjects(name sysname,id int,xtype char(2),uid smallint,
info smallint,status int,base_schema_ver int,replinfo int,parent_obj int,
crdate datetime,ftcatid smallint,schema_ver int,stats_schema_ver int,type char(2),
userstat smallint,sysstat smallint,indexdel smallint,refdate datetime,version int,
deltrig int,instrig int,updtrig int,seltrig int,category int,cache smallint)

INSERT INTO #Tbl_SourceObjects
EXEC sp_executesql @SourceSYSObjectsExecute

IF(OBJECT_ID('tempdb..#Tbl_SourceComments') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_SourceComments
END

CREATE TABLE #Tbl_SourceComments(id int,number smallint,colid smallint,
status smallint,ctext varbinary(8000),texttype smallint,language smallint,
encrypted bit,compressed bit, text nvarchar(4000))

--print @SourceSYSCommentExecute
INSERT INTO #Tbl_SourceComments
EXEC sp_executesql @SourceSYSCommentExecute

--======================================================================

IF(OBJECT_ID('tempdb..#Tbl_TargetObjects') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_TargetObjects
END

CREATE TABLE #Tbl_TargetObjects(name sysname,id int,xtype char(2),uid smallint,
info smallint,status int,base_schema_ver int,replinfo int,parent_obj int,
crdate datetime,ftcatid smallint,schema_ver int,stats_schema_ver int,type char(2),
userstat smallint,sysstat smallint,indexdel smallint,refdate datetime,version int,
deltrig int,instrig int,updtrig int,seltrig int,category int,cache smallint)

INSERT INTO #Tbl_TargetObjects
EXEC sp_executesql @TargetSYSObjectsExecute

IF(OBJECT_ID('tempdb..#Tbl_TargetComments') IS NOT NULL)
BEGIN
DROP TABLE #Tbl_TargetComments
END

CREATE TABLE #Tbl_TargetComments(id int,number smallint,colid smallint,
status smallint,ctext varbinary(8000),texttype smallint,language smallint,
encrypted bit,compressed bit, text nvarchar(4000))

--print @SourceSYSCommentExecute
INSERT INTO #Tbl_TargetComments
EXEC sp_executesql @TargetSYSCommentExecute

--======================================================================

Select @SourceDbName [Source DataBase Name], Main.name [Source Object Name],
@TargetDbName[Target DataBase Name],Main.[Object Defination],SubMain.name [Target Object Name]
,Main.text[Source Object text],SubMain.text [Target Object text],
CASE WHEN Main.text=SubMain.text Then
 Concat(Main.[Object Defination], '  Object Match with Source Databse')
 else
 Concat(Main.[Object Defination], ' Object Mismatch Or Not Found with Source Database')
 end [Global Message]
from
(
SELECT o.name, cs.id,cs.number,cs.colid,cs.status,cs.ctext,cs.texttype,
cs.language,cs.encrypted,cs.compressed,ISNULL(cs.text,'') text,
CASE WHEN o.xtype in (SELECT x.xtype from @Tbl_Xtypes x)
THEN (Select xx.xtypeDefination from @Tbl_Xtypes xx where xx.xtype=o.xtype)
else '' end [Object Defination]
FROM #Tbl_SourceComments cs join
#Tbl_SourceObjects o on o.id=cs.id
) Main
left join
(
select css.id,oo.name,ISNULL(css.text,'') text,oo.xtype FROM
#Tbl_TargetComments css  left join
#Tbl_TargetObjects oo on css.id=oo.id

)SubMain  on Main.name=SubMain.name

--===========================Compair Tables Only--=================

SET @InformationCommonQuery = N'
select TABLE_CATALOG,TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION
,COLUMN_DEFAULT,IS_NULLABLE,DATA_TYPE,CHARACTER_MAXIMUM_LENGTH
,CHARACTER_OCTET_LENGTH,NUMERIC_PRECISION,NUMERIC_PRECISION_RADIX
,NUMERIC_SCALE,DATETIME_PRECISION,ISNULL(CHARACTER_SET_CATALOG,'''')
CHARACTER_SET_CATALOG
,ISNULL(CHARACTER_SET_SCHEMA,'''') CHARACTER_SET_SCHEMA,
ISNULL(CHARACTER_SET_NAME,'''')
CHARACTER_SET_NAME,
ISNULL(COLLATION_CATALOG,'''') COLLATION_CATALOG
,ISNULL(COLLATION_SCHEMA,'''') COLLATION_SCHEMA,ISNULL(COLLATION_NAME,'''')
COLLATION_NAME,ISNULL(DOMAIN_CATALOG,'''') DOMAIN_CATALOG,ISNULL(DOMAIN_SCHEMA,'''')
DOMAIN_SCHEMA
,ISNULL(DOMAIN_NAME,'''') DOMAIN_NAME from INFORMATION_SCHEMA.COLUMNS';

SET @ExecuteSourceInformationColumn = N'USE ['+@SourceDbName+'] '+@InformationCommonQuery+'';
IF(OBJECT_ID('tempdb..#Tbl_SourceInfoTable') Is not null)
BEGIN
DROP TABLE #Tbl_SourceInfoTable
END

CREATE TABLE #Tbl_SourceInfoTable(TABLE_CATALOG nvarchar(256),TABLE_SCHEMA nvarchar(256),
TABLE_NAME sysname,COLUMN_NAME sysname,ORDINAL_POSITION int,
COLUMN_DEFAULT nvarchar(4000),IS_NULLABLE varchar(3),DATA_TYPE nvarchar(256),
CHARACTER_MAXIMUM_LENGTH int,CHARACTER_OCTET_LENGTH int,NUMERIC_PRECISION tinyint,
NUMERIC_PRECISION_RADIX smallint,NUMERIC_SCALE int,DATETIME_PRECISION int,
CHARACTER_SET_CATALOG sysname ,CHARACTER_SET_SCHEMA sysname,
CHARACTER_SET_NAME sysname,COLLATION_CATALOG sysname,COLLATION_SCHEMA sysname,
COLLATION_NAME sysname,DOMAIN_CATALOG sysname,DOMAIN_SCHEMA sysname,
DOMAIN_NAME sysname)

INSERT INTO #Tbl_SourceInfoTable
exec sp_executesql @ExecuteSourceInformationColumn

DECLARE @ExecuteTargetInformationColumn NVARCHAR(4000);

SET @ExecuteTargetInformationColumn = N'USE ['+@TargetDbName+'] '+@InformationCommonQuery+'';
IF(OBJECT_ID('tempdb..#Tbl_TargetInfoTable') Is not null)
BEGIN
DROP TABLE #Tbl_TargetInfoTable
END

CREATE TABLE #Tbl_TargetInfoTable(TABLE_CATALOG nvarchar(256),TABLE_SCHEMA nvarchar(256),
TABLE_NAME sysname,COLUMN_NAME sysname,ORDINAL_POSITION int,
COLUMN_DEFAULT nvarchar(4000),IS_NULLABLE varchar(3),DATA_TYPE nvarchar(256),
CHARACTER_MAXIMUM_LENGTH int,CHARACTER_OCTET_LENGTH int,NUMERIC_PRECISION tinyint,
NUMERIC_PRECISION_RADIX smallint,NUMERIC_SCALE int,DATETIME_PRECISION int,
CHARACTER_SET_CATALOG sysname ,CHARACTER_SET_SCHEMA sysname,
CHARACTER_SET_NAME sysname,COLLATION_CATALOG sysname,COLLATION_SCHEMA sysname,
COLLATION_NAME sysname,DOMAIN_CATALOG sysname,DOMAIN_SCHEMA sysname,
DOMAIN_NAME sysname)

INSERT INTO #Tbl_TargetInfoTable
EXEC sp_executesql @ExecuteTargetInformationColumn

SELECT s.TABLE_NAME [Source Table Name],s.COLUMN_NAME [Source Col Name],
s.DATA_TYPE [Source Col Data Type],
IC.TABLE_NAME [Target Table Name],
IC.COLUMN_NAME [Target Col Name],IC.DATA_TYPE [Target Col Data Type] ,
CASE WHEN s.COLUMN_NAME= IC.COLUMN_NAME
THEN 'Match'
ELse 'Column Mismatch Or Not Found'
end [Global Message Info]
FROM #Tbl_SourceInfoTable s LEFT JOIN
     #Tbl_TargetInfoTable IC
              ON s.COLUMN_NAME=IC.COLUMN_NAME
              AND s.TABLE_NAME=IC.TABLE_NAME

HostForLIFEASP.NET SQL Server 2019 Hosting

When SELECTing information from the table(s), we mostly ask for grouped data. Grouping allows us to retrieve statistical data by some columns. Grouping is very important, but sometimes it is not just enough to “generate” final information.

Sometimes we need to rotate one of the columns to be as a row without changing the result of grouped data. Long story short, transforming data from a state of rows to a state of columns is called Pivoting.

PS: All examples use Adventurework2019.

Simple example without pivot: (classical grouping)
SELECT
  SalesOrderID
, SpecialOfferID
, SUM(UnitPrice) as TotalSum
  FROM Sales.SalesOrderDetail AS SOD
  GROUP BY SalesOrderID, SpecialOfferID

In the above example, we get data grouped by SalesOrderId and SpecialOfferId. Say your manager asked for the same data but he wants to see SpeciallOfferId in the columns. So from the above example, you will have columns like [1], and [2]. The main focus is SpecialOfferId and we need to somehow rotate the column to be not a column but a row.

T-SQL provides a special table operator to implement it: PIVOT.

Before writing PIVOT, let’s see what you need to know about the internal implementation of PIVOT. You should identify 3 elements:

    What do you want to see on rows?
    What do you want to see on columns?
    What type of information should be rendered in the intersection of these columns and rows ( mostly it is your aggregate column)

Here is the syntax for implementing it,

As we learned from the article, Common Table Expression’s (CTE) use cases wider than Derived Tables and now we will implement PIVOT exactly with CTE.

WITH CTE
AS
(SELECT SOd.SalesOrderID, Sod.SpecialOfferID, UnitPrice
  FROM Sales.SalesOrderDetail AS SOD)
  SELECT SalesOrderId, [1],[2] FROM CTE
  PIVOT(SUm(UnitPrice) FOR SpecialOfferId IN ([1],[2])) AS PVT

As you can see, PIVOT is a special table expression and as a complex combination, it defines the aggregate column for the spread column. Before switching to UNPIVOT, we need to understand some limitations of PIVOT:

    We can use only one aggregate with PIVOT
    The IN clause of the PIVOT operator accepts a static list of spreading values. You need to type them manually without any magic technique.
    No way to use COUNT(*), use classical COUNT(<column name>)

Unpivot is a reverse operation for you PIVOT. If you have any table generated by u PIVOT, you can reverse this table to the “original one” with UNPIVOT.

Let’s use our query with some modifications to create a table that will store all PIVOTted data.
WITH CTE
AS
(SELECT SOd.SalesOrderID, Sod.SpecialOfferID, UnitPrice
  FROM Sales.SalesOrderDetail AS SOD)
  SELECT * INTO MyPivottedTable FROM CTE
  PIVOT(SUm(UnitPrice) FOR SpecialOfferId IN ([1],[2])) AS PVT

The above query will store all information inside new created MyPivottedTable table.

Now it is time for our magic UNPIVOT to ”restore” information to its original.
SELECT SalesOrderID,SpecialOfferID, UnitPrice
 FROM MyPivottedTable
 UNPIVOT(UnitPrice FOR SpecialOfferId IN ([1],[2])) AS UNPVT

Here is the response:

HostForLIFEASP.NET SQL Server 2019 Hosting