SQL Tips and Tricks

— TO GET STORED PROCEDURE TEXT/INFORMATION

SP_HELPTEXT  ‘<STORE PROCEDURE NAME>’;

— TO RENAME THE STORED PROCEDURE

SP_RENAME ‘<CURRENT STORED PROCEDURE NAME>’, ‘<NEW STORED PROCEDURE NAME>’;

— TO CHECK TABLE DEPENDENCIES

SELECT DISTINCT SYSOBJ.NAME, SYSOBJ.XTYPE

FROM SYSCOMMENTS SYSCOM

INNER JOIN SYSOBJECTS SYSOBJ ON SYSCOM.ID = SYSOBJ.ID

WHERE SYSCOM.TEXT LIKE ‘%<TABLE NAME>%’;

— TO FIND TABLE NAME CONTAINS COLUMN NAME

SELECT COLUMN_NAME, TABLE_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE COLUMN_NAME LIKE ‘%<COLUMN NAME>%’;

— TO SEARCH STORED PROCEDURE CONTAINING A CPARTICULAR TEXT

SELECT ROUTINE_NAME, ROUTINE_DEFINITION

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE ‘%<COLUMN NAME>%’

AND ROUTINE_TYPE = ‘procedure’;

— TO GET SEQUENCE NO

SELECT ROW_NUMBER() OVER(PARTITION BY OfficeLocation ORDER BY UserID ASC) AS RecordNo , UserID , UserEmail , OfficeLocation , UserName FROM dbo.UserMaster;

— TO SEARCH STORED PROCEDURE CONTAINING A CPARTICULAR TEXT

Getting the next value of the primary key from MSSQL table

SELECT IDENT_CURRENT(‘<Table Name>‘);

SELECT IDENT_CURRENT(‘<Table Name>‘)+IDENT_INCR(‘<Table Name>‘);

SELECT MAX(<primary key column name>) FROM <Table Name>;

This won’t work if the database is empty, because it returns NULL.

⇒ Please note that this method can be used to predict the next id, but does not guarantee this return value. Because the time you execute request and time it take to display result, another transaction occurred, making return value null and void.

IDENT_CURRENT (Transact-SQL)

Returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

Alternate Key, Candidate key, Composite Key, Foreign Key, Primary Key in RDBMS

We use the database to organize information. To maintain data in a correct and well formed, we use concept of keys. There are five types of keys in database which are as follows –

  • Alternate Key
  • Candidate key
  • Composite Key
  • Foreign Key
  • Primary Key

To understand keys, we will take EmployeeMaster table as example –

EmployeeMaster
{
EmployeeID,
EmpFirstName,
EmpLastName,
DesignationID
}

Table keys are:

Alternate Key:  EmpFirstName + EmpLastName

Candidate keys: EmployeeID or EmpFirstName + EmpLastName

Composite Key:  EmpFirstName + EmpLastName

Foreign Key: DesignationID

Primary Key: EmployeeID

There are five types of keys in database which are as follows –

Alternate Key

  • Any table have more than one candidate key, then after choosing primary key from those candidate key, rest of candidate keys are known as an alternate key of  that table
  • For e.g. EmpFirstName + EmpLastName

Candidate key

  • Is not null
  • Have unique records
  • Can create multiple candidate key per table
  • Candidate keys are those keys which is a candidate for primary key of a table
  • For e.g. EmployeeID or EmpFirstName + EmpLastName

Composite Key

  • When we create keys on more than one column then that key is known as composite key
  • For e.g. EmpFirstName + EmpLastName

Foreign Key

  • Can create more than one foreign key per table
  • Foreign key are those keys which is used to define relationship between two tables
  • To implement relationship between two tables
  • Also known as referential integrity
  • For e.g. DesignationID which is reference to DesignationMaster table

Primary Key

  • Is not null
  • Have unique records
  • Only one primary key per table
  • Candidate key which is chosen as a primary key for table is known as primary key
  • For e.g. EmployeeID

Replace multiple space with single space – SQL Server

Replace multiple space with single space – SQL Server

DECLARE @Temp TABLE (OriginalString VARCHAR(8000))

INSERT INTO @Temp (OriginalString)
SELECT ‘ Hello. This is Hello World. ‘

SELECT REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(OriginalString)), ‘ ‘, ‘ ‘ + CHAR(7)), CHAR(7) + ‘ ‘, ”), CHAR(7), ”) AS [result]
FROM @Temp
WHERE CHARINDEX(‘ ‘, OriginalString) > 0

Happy Coding 🙂