Archive for the ‘SQL Tips and Tricks’ Category

SQL Server – System Stored Procedure Script

System Stored Procedure Script

Have you seen script of System Stored Procedure of SQL Server? Well if you are curious creature like me, then why not see it.

To do that, we need to use another System Stored Procedure sp_helptext. It will display the script behind the specified System Stored Procedure.

Syntax:

EXEC sp_helptext ‘System Stored Procedure’

Example:

– – To see the script of sp_helplogins

EXEC sp_helptext ‘master..sp_helplogins’

Go ahead and try it with other System Stored Procedures.

Did you see how Microsoft codes their Stored Procedures? If you think “Even I can do this stuff“, feel luck and please forward your resume to Microsoft Corporation! 🙂

Good Luck!

SQL Server – List all Foreign Key Constraints of Database or Table

List all Foreign Key Constraints of Database or Table

How often we face this problem? The Foreign Key constraint of the table preventing us from deleting or modifying records.

To deal with them, first we need to have information about these constraints. How nice it would it be to get details of all the Foreign Key constraint defined in the Database or on a Table.

Good News, yes we can. Just use following script:

USE AdventureWorks

— List all Foreign Key Constraints in whole Database

SELECT o1.name AS Referencing_Object_name,

c1.name AS referencing_column_Name,

o2.name AS Referenced_Object_name,

c2.name AS Referenced_Column_Name,

s.name AS Constraint_name

FROM sysforeignkeys fk

INNER JOIN sysobjects o1 ON fk.fkeyid = o1.id

INNER JOIN sysobjects o2 ON fk.rkeyid = o2.id

INNER JOIN syscolumns c1 ON c1.id = o1.id AND c1.colid = fk.fkey

INNER JOIN syscolumns c2 ON c2.id = o2.id AND c2.colid = fk.rkey

INNER JOIN sysobjects s ON fk.constid = s.id


— List all Foreign Key Constraints in Table Customer

SELECT o1.name AS Referencing_Object_name,

c1.name AS referencing_column_Name,

o2.name AS Referenced_Object_name,

c2.name AS Referenced_Column_Name,

s.name AS Constraint_name

FROM sysforeignkeys fk

INNER JOIN sysobjects o1 ON fk.fkeyid = o1.id

INNER JOIN sysobjects o2 ON fk.rkeyid = o2.id

INNER JOIN syscolumns c1 ON c1.id = o1.id AND c1.colid = fk.fkey

INNER JOIN syscolumns c2 ON c2.id = o2.id AND c2.colid = fk.rkey

INNER JOIN sysobjects s ON fk.constid = s.id

AND o2.name=‘Customer’

See also:

SQL Server – List all Constraints of Database or Table

SQL Server – Drop All Table Constraints

Good Luck!

SQL Server – Drop All Table Constraints

Drop All Table Constraints

You want to redefine all the constraints set on a Table. To do that you need to first drop all the existing constraints set on the table. Following is the simple script to do so the same:

— To drop all Constraints on a table Employee

DECLARE @database NVARCHAR(50)

DECLARE @table NVARCHAR(50)

DECLARE @sql NVARCHAR(255)

SET @database = ‘DB_MyDatabase’

SET @table = ‘Employee’

WHILE EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE constraint_catalog=@database AND table_name=@table)

BEGIN

SELECT @sql = ‘ALTER TABLE ‘ + @table + ‘ DROP CONSTRAINT ‘ + CONSTRAINT_NAME

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE constraint_catalog=@database AND table_name=@table

EXEC sp_executesql @sql

END

The above script does not drop DEFAULT constraint set on the Table.

See also:

SQL Server – List all Constraints of Database or Table

SQL Server – List all Foreign Key Constraints of Database or Table

Good Luck!

SQL Server – List all Constraints of Database or Table

List all Constraints of Database or Table

Many times when we are using DML commands like: INSERT, UPDATE, DELETE, we get errors because one or the other constraint is set on the table. It get really annoying when we are new to a Database and don’t know what constraints are set on the tables.

It would be of great help if we get the list of all the constraints in the database. Below are two methods to do the same.

Method 1

Use sys.objects to get the constraint information.

— To Display all the Constraint in the Database

SELECT * FROM sys.objects

WHERE type_desc LIKE ‘%CONSTRAINT’

The above query will display all the fields of sys.objects. We can refine the above query to display the result more tasteful.

— To Display all the Constraints in the Database

SELECT OBJECT_NAME(object_id) AS ConstraintName,

SCHEMA_NAME(schema_id) AS SchemaName,

OBJECT_NAME(parent_object_id) AS TableName,

type_desc AS ConstraintType

FROM sys.objects

WHERE type_desc LIKE %CONSTRAINT’

— To Display all the Constraints in table ‘Employee’

SELECT OBJECT_NAME(object_id) AS ConstraintName,

SCHEMA_NAME(schema_id) AS SchemaName,

type_desc AS ConstraintType

FROM sys.objects

WHERE type_desc LIKE ‘%CONSTRAINT’ AND OBJECT_NAME(parent_object_id)=‘Employee’

Method 2

Use INFORMATION_SCHEMA.TABLE_CONSTRAINTS to get the constraint information. This query has one shortcoming; it won’t display DEFAULT constraints of the database

— To Display all the Constraints in the Database

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

— To Display all the Constraints in table ‘Employee’

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS

WHERE TABLE_NAME=‘Employee’

To display DEFAULT constraints in the database we can use following SQL Statement.

— To Display Default Constraints in Database

SELECT OBJECT_NAME(PARENT_OBJECT_ID) AS TABLE_NAME,

COL_NAME (PARENT_OBJECT_ID, PARENT_COLUMN_ID) AS COLUMN_NAME,

NAME AS DEFAULT_CONSTRAINT_NAME

FROM SYS.DEFAULT_CONSTRAINTS

See also:

SQL Server – Drop All Table Constraints

SQL Server – Stored Procedure to Drop All Database Constraints

Good Luck!

SQL Server – Find Nth Highest or Lowest Value in Table

Find Nth Highest or Lowest Value in Table

This one is a tricky question. We all assume that this is not a big deal, but when this question is asked in the interviews, we try all the permutations and combinations of SELECT statements, but are never close to the answer.

There are many solutions to this problem. I have listed down some of the simple methods to find out Nth highest and Nth Lowest values in the table.

Method 1

USE AdventureWorks;

— Finding unique third highest UnitPrice value from table

SELECT TOP 1 UnitPrice FROM

(SELECT DISTINCT TOP 3 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice DESC) AS A

ORDER BY UnitPrice;

— Finding unique second lowest UnitPrice value from table

SELECT TOP 1 UnitPrice FROM

(SELECT DISTINCT TOP 2 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice) AS A

ORDER BY UnitPrice DESC;

Method 2

— Finding unique third highest UnitPrice value from table

SELECT TOP 1 UnitPrice FROM Purchasing.PurchaseOrderDetail WHERE UnitPrice IN

(SELECT DISTINCT TOP 3 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice DESC)

ORDER BY UnitPrice;

— Finding unique second lowest UnitPrice value from table

SELECT TOP 1 UnitPrice FROM Purchasing.PurchaseOrderDetail WHERE UnitPrice IN

(SELECT DISTINCT TOP 2 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice)

ORDER BY UnitPrice DESC;

Method 3

— Finding unique third highest UnitPrice value from table

SELECT MIN(UnitPrice) FROM

(SELECT DISTINCT TOP 3 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice DESC) AS A;

— Finding unique second lowest UnitPrice value from table

SELECT MAX(UnitPrice) FROM

(SELECT DISTINCT TOP 2 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice) AS A;

Method 4

— Finding unique third highest UnitPrice value from table

SELECT MIN(UnitPrice) FROM Purchasing.PurchaseOrderDetail WHERE UnitPrice IN

(SELECT DISTINCT TOP 3 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice DESC);

— Finding unique second lowest UnitPrice value from table

SELECT MAX(UnitPrice) FROM Purchasing.PurchaseOrderDetail WHERE UnitPrice IN

(SELECT DISTINCT TOP 2 UnitPrice FROM Purchasing.PurchaseOrderDetail ORDER BY UnitPrice);

Method 5

— Finding unique third highest UnitPrice value from table

SELECT MAX(P1.UnitPrice) FROM Purchasing.PurchaseOrderDetail P1

WHERE 3 <= (SELECT COUNT(DISTINCT P2.UnitPrice)

FROM Purchasing.PurchaseOrderDetail P2 WHERE P1.UnitPrice <= P2.UnitPrice);

Method 6

— Finding unique third highest UnitPrice value from table

SELECT DISTINCT P1.UnitPrice FROM Purchasing.PurchaseOrderDetail P1

WHERE 3 = (SELECT COUNT(DISTINCT P2.UnitPrice)

FROM Purchasing.PurchaseOrderDetail P2 WHERE P1.UnitPrice<=P2.UnitPrice);

Hope you find this useful.

Good Luck!

SQL Server – How to Handle Single Quote in String

How to Handle Single Quote in String

This is the common one-time problem (not for all ;-)) faced by a new DBA Developer, and even I did.

The solution to this problem is rather simple. Use an extra single quote, with the quote character occurring in the string.

See the example below:

— The Insert Satement below will give and error

INSERT INTO Employee VALUES (1003, ‘Patrick’, ‘O’Brain’)

Error Message

Msg 105, Unclosed quotation mark after the character string

— Correct Statement

INSERT INTO Employee VALUES (1003, ‘Patrick’, ‘O”Brain’)

— Similarly you can use it in WHERE clause also

SELECT * FROM Employee WHERE LastName=’O”Brain’

See also:

Function to split comma delimited String or List

How to handle single quote in sqlquery string?

Good Luck!

SQL Server – Dates in Different Formats

Date in Different Formats

Dates are very frequently used and saved in the Database tables. Saving date using DATETIME datatype is very simple. But it gets really tricky when we want to convert that date and use it in different formats as per our requirements.

The CONVERT command comes to our rescue over here. Following is the syntax for CONVERT command.

CONVERT (length_of_output, date, format_code)

Use of CONVERT command to convert Current Date into CHAR datatype and display it in different formats using format_code is show below:

SELECT CONVERT(CHAR(100), GETDATE(), 0 ) AS YouGotDate — Displays — Aug 19 2009 6:46AM
SELECT CONVERT(CHAR(100), GETDATE(), 1 ) AS YouGotDate — Displays — 08/19/09
SELECT CONVERT(CHAR(100), GETDATE(), 2 ) AS YouGotDate — Displays — 09.08.19
SELECT CONVERT(CHAR(100), GETDATE(), 3 ) AS YouGotDate — Displays — 19/08/09
SELECT CONVERT(CHAR(100), GETDATE(), 4 ) AS YouGotDate — Displays — 19.08.09
SELECT CONVERT(CHAR(100), GETDATE(), 5 ) AS YouGotDate — Displays — 19-08-09
SELECT CONVERT(CHAR(100), GETDATE(), 6 ) AS YouGotDate — Displays — 19 Aug 09
SELECT CONVERT(CHAR(100), GETDATE(), 7 ) AS YouGotDate — Displays — Aug 19, 09
SELECT CONVERT(CHAR(100), GETDATE(), 8 ) AS YouGotDate — Displays — 06:46:45
SELECT CONVERT(CHAR(100), GETDATE(), 9 ) AS YouGotDate — Displays — Aug 19 2009 6:46:45:507AM
SELECT CONVERT(CHAR(100), GETDATE(), 10 ) AS YouGotDate — Displays — 08-19-09
SELECT CONVERT(CHAR(100), GETDATE(), 11 ) AS YouGotDate — Displays — 09/08/19
SELECT CONVERT(CHAR(100), GETDATE(), 12 ) AS YouGotDate — Displays — 090819
SELECT CONVERT(CHAR(100), GETDATE(), 13 ) AS YouGotDate — Displays — 19 Aug 2009 06:46:45:507
SELECT CONVERT(CHAR(100), GETDATE(), 14 ) AS YouGotDate — Displays — 06:46:45:507
SELECT CONVERT(CHAR(100), GETDATE(), 20 ) AS YouGotDate — Displays — 2009-08-19 06:46:45
SELECT CONVERT(CHAR(100), GETDATE(), 21 ) AS YouGotDate — Displays — 2009-08-19 06:46:45.540
SELECT CONVERT(CHAR(100), GETDATE(), 22 ) AS YouGotDate — Displays — 08/19/09 6:46:45 AM
SELECT CONVERT(CHAR(100), GETDATE(), 23 ) AS YouGotDate — Displays — 2009-08-19
SELECT CONVERT(CHAR(100), GETDATE(), 24 ) AS YouGotDate — Displays — 06:46:45
SELECT CONVERT(CHAR(100), GETDATE(), 25 ) AS YouGotDate — Displays — 2009-08-19 06:46:45.540
SELECT CONVERT(CHAR(100), GETDATE(), 100 ) AS YouGotDate — Displays — Aug 19 2009 6:46AM
SELECT CONVERT(CHAR(100), GETDATE(), 101 ) AS YouGotDate — Displays — 08/19/2009
SELECT CONVERT(CHAR(100), GETDATE(), 102 ) AS YouGotDate — Displays — 2009.08.19
SELECT CONVERT(CHAR(100), GETDATE(), 103 ) AS YouGotDate — Displays — 19/08/2009
SELECT CONVERT(CHAR(100), GETDATE(), 104 ) AS YouGotDate — Displays — 19.08.2009
SELECT CONVERT(CHAR(100), GETDATE(), 105 ) AS YouGotDate — Displays — 19-08-2009
SELECT CONVERT(CHAR(100), GETDATE(), 106 ) AS YouGotDate — Displays — 19 Aug 2009
SELECT CONVERT(CHAR(100), GETDATE(), 107 ) AS YouGotDate — Displays — Aug 19, 2009
SELECT CONVERT(CHAR(100), GETDATE(), 108 ) AS YouGotDate — Displays — 06:46:45
SELECT CONVERT(CHAR(100), GETDATE(), 109 ) AS YouGotDate — Displays — Aug 19 2009 6:46:45:913AM
SELECT CONVERT(CHAR(100), GETDATE(), 110 ) AS YouGotDate — Displays — 08-19-2009
SELECT CONVERT(CHAR(100), GETDATE(), 111 ) AS YouGotDate — Displays — 2009/08/19
SELECT CONVERT(CHAR(100), GETDATE(), 112 ) AS YouGotDate — Displays — 20090819
SELECT CONVERT(CHAR(100), GETDATE(), 113 ) AS YouGotDate — Displays — 19 Aug 2009 06:46:45:930
SELECT CONVERT(CHAR(100), GETDATE(), 114 ) AS YouGotDate — Displays — 06:46:45:930
SELECT CONVERT(CHAR(100), GETDATE(), 120 ) AS YouGotDate — Displays — 2009-08-19 06:46:45
SELECT CONVERT(CHAR(100), GETDATE(), 121 ) AS YouGotDate — Displays — 2009-08-19 06:46:45.943
SELECT CONVERT(CHAR(100), GETDATE(), 126 ) AS YouGotDate — Displays — 2009-08-19T06:46:45.990
SELECT CONVERT(CHAR(100), GETDATE(), 130 ) AS YouGotDate — Displays — 15 ????? 1419 6:46:46:040AM
SELECT CONVERT(CHAR(100), GETDATE(), 131 ) AS YouGotDate — Displays — 15/08/1419 6:46:46:040AM
SELECT CONVERT(CHAR(100), GETDATE(), 256 ) AS YouGotDate — Displays — Aug 19 2009 6:46AM

Spoilt for choice baby! Correct.