SP in Fundamental level in SQL Server

SP_RENAMEDB: Here SP stands for Stored Procedure. This stored procedure is used to change the name of the existing database.

Syntax: SP_RENAMEDB ‘OLD DATABASENAME’, ‘NEW DATABASENAME’

            E.g.: SP_RENAMEDB  ‘NRSTT’, ‘NRSTTS’

The above statement renames (changes the database name) NRSTT to NRSTTS

SP_RENAME: This stored procedure is used for changing the name of the table and for changing the name of the column

i. Syntax to change the name of the table

SP_RENAME ‘OLD TABLENAME’, ‘NEW TABLENAME’

E.g.      SP_RENAME ‘EMP’, ‘EMPLOY’

The above stored procedure changes the name of EMP table to EMPLOY

ii. Syntax to change the name of the column

SP_RENAME ‘TABLE.OLDCOLUMN NAME’, ‘NEW COLUMNNAME’

E.g.      SP_RENAME ‘STUDENT.ADR’, ‘ADDRESS’

The above stored procedure changes the name of ADR column to ADDRESS in STUDENT table.

SP_HELP: This stored procedure is used to display the description of a specific table.

Syntax: SP_HELP TABLENAME

E.g.: SP_HELP EMP

The above stored procedure displays the description of EMP table

SP_DATADASES: This Stored procedure displays the list of databases available in SQL Server.

Syntax: SP_DATABASES

SP_TABLES: This stored procedure displays the list of tables available in the current database.

Syntax: SP_TABLES

SP_HELPDB: This stored procedure is used to display the description of  master and log data file information of a specific database

Syntax: SP_HELPDB Database-Name

Ex: SP_HELPDB SAMPLE

SP_SPACEUSED: This stored procedure is used to find the memory status of the current database

Syntax: SP_SPACEUSED

How to Install SQL Server on Linux CentOS 7 - SQL Server on Linux

# Let's check if mssql-server repository already exists?

sudo ls /etc/yum.repos.d | grep mssql-server.repo

# If it does, we need to check contents of this repository and remove that, otherwise we will face issues during the install

sudo cat/etc/yum.repos.d/mssql-server.repo

sudo rm -rf /etc/yum.repos.d/mssql-server.repo

# Configure and Download SQL Server Repository

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config...

# Install SQL Server using below commands

sudo yum install -y mssql-server

# Configure SQL Server and Setup SA password

sudo /opt/mssql/bin/mssql-conf setup

# Restart SQL Server Services

systemctl restart mssql-server

# Configure repository and install Sqlcmd and other Tools to internally connect to newly installed SQL Server

sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config...

sudo yum install -y mssql-tools unixODBC-devel

#Set Environment Tools to your Path Environment


# Connect to SQL Server using sqlcmd tool

sqlcmd -S localhost -U SA -P Pass@123


# Connect to SQL Server Remotely using SQL Server Authentication

SSMS - Error and Resolution

# Enable Firewall port 1433


sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
sudo firewall-cmd --reload



# Enable and start SQL Server Agent services
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true


install sql server on centos 7
install sql server  2017 on CentOS 7
install sql server management studio on linux

How To Find The Highest Salary In SQL Server

Introduction:

In this blog we will discuss how to find the highest salary & the second highest salary..

Query to find the highest salary:

SELECT * FROM EMPLOYEE ORDER BY SALARY DESC  

SELECT MAX(SALARY) FROM EMPLOYEE   

Query to find second highest salary:

SELECT*FROM EMPLOYEE ORDER BY SALARY DESC  

SELECT MAX(SALARY) FROM EMPLOYEE  

WHERE SALARY<(SELECT MAX(SALARY) FROM EMPLOYEE)