Frequently Used Queries In SQL Server

Jump To Right Section Show

This article lists out the frequently used queries in SQL Server Developers. I hope you will like this article and if any of your commonly used queries are not listed here, please let me know. I will post it and we can help together to the SQL Server Developers Community.

1. How to check if a Database exists in SQL Server

We can use DB_ID() function like below to check if a database exists. Here in the below script, we are checking the existence of the database SqlHintsDB. We can as well use the sys. databases Catalog View to check the existence of the database.

IF DB_ID('SqlHintsDB') IS NOT NULL
BEGIN
    PRINT 'Database Exists'
END
ELSE
BEGIN
 PRINT 'Database Not Exists'
END



1
2
3
4
5
6
7
8
9
10

 
IF DB_ID('SqlHintsDB') IS NOT NULL
BEGIN
    PRINT 'Database Exists'
END
ELSE
BEGIN
 PRINT 'Database Not Exists'
END
 


2. How to check if a Table exists in SQL Server

We can use sys.tables catalog view to check the existence of the Table. Here in the below script, we are checking the existence of the table Customers

IF EXISTS(SELECT 1 FROM sys.Tables WHERE  Name = N'Customers' AND Type = N'U')
BEGIN
  PRINT 'Table Exists'
END
ELSE
BEGIN
 PRINT 'Table Not Exists'
END



1
2
3
4
5
6
7
8
9
10

 
IF EXISTS(SELECT 1 FROM sys.Tables WHERE  Name = N'Customers' AND Type = N'U')
BEGIN
  PRINT 'Table Exists'
END
ELSE
BEGIN
 PRINT 'Table Not Exists'
END
 


 3. How to check if a Temp table exists in SQL Server

The below script shows how we can check the existence of a Temporary Table. As we know temp tables are created in the TempDB database, so we need to check the existence of the temp table in the TempDB database. In the below script we are checking the existence of the temp table #TempTable

IF OBJECT_ID('TempDB.dbo.#TempTable') IS NOT NULL
BEGIN
  PRINT '#TempTable Temporary Table Exists'
END
ELSE
BEGIN
   PRINT '#TempTable Temporary Table not Exists'
END
GO



1
2
3
4
5
6
7
8
9
10
11

 
IF OBJECT_ID('TempDB.dbo.#TempTable') IS NOT NULL
BEGIN
  PRINT '#TempTable Temporary Table Exists'
END
ELSE
BEGIN
   PRINT '#TempTable Temporary Table not Exists'
END
GO
 


4. How to check if a Stored Procedure exists in SQL Server

We can use the sys.procedures catalog view to check the existence of a stored procedure. Here in the below script, we are checking the existence of the stored procedure GetCustomers

USE SqlHintsDemoDB
GO
IF EXISTS(SELECT 1 FROM sys.procedures WHERE Name = 'GetCustomers')
BEGIN
    PRINT 'Stored Procedure Exists'
END
ELSE
BEGIN
    PRINT 'Stored Procedure NOT Exists'
END



1
2
3
4
5
6
7
8
9
10
11
12

 
USE SqlHintsDemoDB
GO
IF EXISTS(SELECT 1 FROM sys.procedures WHERE Name = 'GetCustomers')
BEGIN
    PRINT 'Stored Procedure Exists'
END
ELSE
BEGIN
    PRINT 'Stored Procedure NOT Exists'
END
 

5. How to check if a Function exists in SQL Server

We can use the sys.objects catalog view to check the existence of a User Defined Function. Here in the below script, we are checking the existence of the User Defined Function GetEmployeeDetail


USE SqlHintsFunctionExists
GO
IF EXISTS (SELECT 1 FROM sys.objects
           WHERE Name = 'GetEmployeeDetail'
             AND Type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
BEGIN
    PRINT 'User defined function Exists'
END
ELSE
BEGIN
PRINT 'User defined function Exists'
END

USE SqlHintsFunctionExists

IF EXISTS (SELECT 1 FROM sys.objects

WHERE Name = 'GetEmployeeDetail'

AND Type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))

BEGIN

PRINT 'User defined function Exists'

END

ELSE

BEGIN

PRINT 'User defined function Exists'

END

6. How to check if a VIEW exists in SQL Server

We can use the sys.views catalog view to check the existence of a View. Here in the below script, we are checking the existence of the View vwGetCustomerInfo

IF EXISTS(SELECT 1 FROM sys.views
     WHERE Name = 'vwGetCustomerInfo')
    BEGIN
        PRINT 'View Exists'
    END
ELSE
    BEGIN
        PRINT 'View NOT Exists'
    END



1
2
3
4
5
6
7
8
9
10
11

 
IF EXISTS(SELECT 1 FROM sys.views
     WHERE Name = 'vwGetCustomerInfo')
    BEGIN
        PRINT 'View Exists'
    END
ELSE
    BEGIN
        PRINT 'View NOT Exists'
    END
 

7. How to check if an Index exists in SQL Server

We can use sys.indexes catalog view to check the existence of a Clustered and Non-Clustered indexes. We can execute a query like below to check the existence of an Index IX_Customer_Id on the Customer table created with a default schema (i.e. dbo).

IF EXISTS (SELECT 1
            FROM sys.indexes I
                INNER JOIN sys.tables T
                    ON I.object_id = T.object_id
                INNER JOIN sys.schemas S
                    ON S.schema_id = T.schema_id
            WHERE I.Name = 'IX_Customer_Id' -- Index name
                AND T.Name = 'Customer' -- Table name
                AND S.Name = 'dbo') --Schema Name
BEGIN
    PRINT 'Index Exists!'
END
ELSE
BEGIN
    PRINT 'Index NOT Exists!'
END



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

 
IF EXISTS (SELECT 1
            FROM sys.indexes I
                INNER JOIN sys.tables T
                    ON I.object_id = T.object_id
                INNER JOIN sys.schemas S
                    ON S.schema_id = T.schema_id
            WHERE I.Name = 'IX_Customer_Id' -- Index name
                AND T.Name = 'Customer' -- Table name
                AND S.Name = 'dbo') --Schema Name
BEGIN
    PRINT 'Index Exists!'
END
ELSE
BEGIN
    PRINT 'Index NOT Exists!'
END
 

8. How to find all the tables with no indexes in SQL Server

We can write a query like below to get all the Tables in the Database that don’t have any indexes:

SELECT Name 'Tables without any Indexes'
FROM SYS.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasIndex')=0



1
2
3
4
5

 
SELECT Name 'Tables without any Indexes'
FROM SYS.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasIndex')=0
 

9. How to find all the indexes that have included columns

We can write a query like below to get the name of all the indexes that have included columns in it and the name of the table to which the index belongs to:

SELECT DISTINCT T.Name 'Table Name',
        I.Name 'Index Name',
        I.type_desc 'Index Type',
        C.Name 'Included Column Name'
FROM sys.indexes I
 INNER JOIN sys.index_columns IC
  ON  I.object_id = IC.object_id AND I.index_id = IC.index_id
 INNER JOIN sys.columns C
  ON IC.object_id = C.object_id and IC.column_id = C.column_id
 INNER JOIN sys.tables T
  ON I.object_id = T.object_id
WHERE is_included_column = 1
ORDER BY T.Name, I.Name



1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

 
SELECT DISTINCT T.Name 'Table Name',
        I.Name 'Index Name',
        I.type_desc 'Index Type',
        C.Name 'Included Column Name'
FROM sys.indexes I
 INNER JOIN sys.index_columns IC
  ON  I.object_id = IC.object_id AND I.index_id = IC.index_id
 INNER JOIN sys.columns C
  ON IC.object_id = C.object_id and IC.column_id = C.column_id
 INNER JOIN sys.tables T
  ON I.object_id = T.object_id
WHERE is_included_column = 1
ORDER BY T.Name, I.Name
 

10. How to find all the filtered indexes or all the tables having filtered indexes in SQL Server

We can write a query like below to get the name of all the filtered indexes or all the tables having filtered indexes in SQL Server:

SELECT DISTINCT T.Name 'Table Name',
  I.Name 'Filtered Index Name',
  I.Filter_Definition 'Filter Definition'
FROM sys.indexes I     
      INNER JOIN sys.tables T
        ON I.object_id = T.object_id
WHERE I.has_filter = 1
ORDER BY T.Name, I.Name



1
2
3
4
5
6
7
8
9
10

 
SELECT DISTINCT T.Name 'Table Name',
  I.Name 'Filtered Index Name',
  I.Filter_Definition 'Filter Definition'
FROM sys.indexes I     
      INNER JOIN sys.tables T
        ON I.object_id = T.object_id
WHERE I.has_filter = 1
ORDER BY T.Name, I.Name
 

11. How to get all HEAP Tables or Tables without Clustered Index in SQL Server

A Table that doesn’t have a Clustered Index is referred to as a HEAP Table. We can write a query like below to get all the HEAP Tables or tables that don’t have Clustered Index:

SELECT T.Name 'HEAP TABLE'
FROM sys.indexes I     
    INNER JOIN sys.tables T
        ON I.object_id = T.object_id
WHERE I.type = 0 AND T.type = 'U'



1
2
3
4
5
6
7

 
SELECT T.Name 'HEAP TABLE'
FROM sys.indexes I     
    INNER JOIN sys.tables T
        ON I.object_id = T.object_id
WHERE I.type = 0 AND T.type = 'U'
 

12. How to get all the Tables with Primary Key Constraint in SQL Server

We can write a query like below to get all the Tables with Primary key constraint:

SELECT T.name 'Table with Primary Key'
FROM SYS.Tables T
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 1
AND type = 'U'



1
2
3
4
5
6

 
SELECT T.name 'Table with Primary Key'
FROM SYS.Tables T
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 1
AND type = 'U'
 

13. How to get all the Tables without Primary Key Constraint in SQL Server

We can write a query like below to get all the Tables without Primary key constraint:

SELECT T.name 'Table without Primary Key'
FROM SYS.Tables T
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
      AND type = 'U'



1
2
3
4
5
6

 
SELECT T.name 'Table without Primary Key'
FROM SYS.Tables T
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey') = 0
      AND type = 'U'
 

14. How to get all the Tables with Non-Clustered Indexes in SQL Server

We can write a query like below to get all the Tables with Non-Clustered indexes:


--List of all the Tables that have Non-Clustered Indexes
SELECT Name 'Tables with Non-Clustered Indexes'
FROM SYS.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasNonclustIndex') = 1
AND Type = 'U'

--List of all the Tables that have Non-Clustered Indexes

SELECT Name 'Tables with Non-Clustered Indexes'

FROM SYS.tables

WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasNonclustIndex') = 1

AND Type = 'U'

15. How to get all the Tables without any Non-Clustered Indexes in SQL Server

We can write a query like below to get all the Tables without any Non-Clustered indexes:

--List of all the Tables with NO Non-Clustered Indexes
SELECT Name 'Tables without any Non-Clustered Indexes'
FROM SYS.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasNonclustIndex') = 0
AND Type = 'U'



1
2
3
4
5
6
7

 
--List of all the Tables with NO Non-Clustered Indexes
SELECT Name 'Tables without any Non-Clustered Indexes'
FROM SYS.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasNonclustIndex') = 0
AND Type = 'U'
 

16. How to get all the Tables with an Identity column in SQL Server

We can write a query like below to get all the Tables with Identity column:

SELECT name 'Table with Identity column'
FROM SYS.Tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
AND type = 'U'



1
2
3
4
5
6

 
SELECT name 'Table with Identity column'
FROM SYS.Tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 1
AND type = 'U'
 

17. How to get all the Tables without an Identity column in SQL Server

We can write a query like below to get all the Tables without Identity column:


SELECT name 'Table without Identity column'
FROM SYS.Tables
WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 0
AND type = 'U'

SELECT name 'Table without Identity column'

FROM SYS.Tables

WHERE OBJECTPROPERTY(object_id,'TableHasIdentity') = 0

AND type = 'U'

18. How to find all the Stored Procedures having a given text in it

We can write a script like below to get all the stored Procedures having a given text in its definition. Here we are searching for the text SearchString in all the stored procedures

SELECT OBJECT_NAME(object_id),
       OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%SearchString%'



1
2
3
4
5
6

 
SELECT OBJECT_NAME(object_id),
       OBJECT_DEFINITION(object_id)
FROM sys.procedures
WHERE OBJECT_DEFINITION(object_id) LIKE '%SearchString%'
 

19. How to find all tables that have specified column name in SQL Server

We can use a script like below to find all the tables in the database that have a column with specified name in it. Here we are searching for all the tables that have columns with a name having a text ColumnName in it.

SELECT SCHEMA_NAME(schema_id) + '.' + t.name AS 'Table Name'
FROM sys.tables t
     INNER JOIN sys.columns c
        ON c.object_id = t.object_id
WHERE c.name like '%ColumnName%'
ORDER BY 'Table Name'



1
2
3
4
5
6
7
8

 
SELECT SCHEMA_NAME(schema_id) + '.' + t.name AS 'Table Name'
FROM sys.tables t
     INNER JOIN sys.columns c
        ON c.object_id = t.object_id
WHERE c.name like '%ColumnName%'
ORDER BY 'Table Name'
 

20. How to find all dependencies of a table in SQL Server

We can use the Dynamic Management Function sys.dm_sql_referencing_entities to get all the entities in the current database that refer to the specified table. In this script, we are trying to get the Employee tables dependencies


SELECT referencing_schema_name, referencing_entity_name,
 referencing_id, referencing_class_desc
FROM sys.dm_sql_referencing_entities
             ('dbo.Employee', 'OBJECT')
GO

SELECT referencing_schema_name, referencing_entity_name,

referencing_id, referencing_class_desc

FROM sys.dm_sql_referencing_entities

('dbo.Employee', 'OBJECT')

21. How to find referenced/dependent objects (like Table, Function, etc) of a Stored Procedure/Function in SQL Server

We can use the Dynamic Management Function sys.dm_sql_referenced_entities to get all the entities in the current database which are referenced by a stored procedure or function. Now we can use a script like below to find all the entities in the current database which are referenced by the stored procedure dbo.GetEmployeeDetails

SELECT referenced_schema_name, referenced_entity_name,
 referenced_minor_name
FROM sys.dm_sql_referenced_entities
              ('dbo.GetEmployeeDetails', 'OBJECT')
GO



1
2
3
4
5
6
7

 
SELECT referenced_schema_name, referenced_entity_name,
 referenced_minor_name
FROM sys.dm_sql_referenced_entities
              ('dbo.GetEmployeeDetails', 'OBJECT')
GO
 

22. How to get the first day of the previous quarter

--First day of the previous quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, 0)

1
2
3
4

--First day of the previous quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) - 1, 0)

23. How to get the first day of the current quarter

--First day of the current quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)

1
2
3
4

--First day of the current quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0)

24. How to get the first day of the next quarter

--First day of the next quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 1, 0)

1
2
3
4

--First day of the next quarter
SELECT DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) + 1, 0)

25. How to get the first day of the quarter for any given date

DECLARE @date DATETIME
SET @date = '07/28/2016'
SELECT DATEADD(qq, DATEDIFF(qq, 0, @date), 0)



1
2
3
4
5

 
DECLARE @date DATETIME
SET @date = '07/28/2016'
SELECT DATEADD(qq, DATEDIFF(qq, 0, @date), 0)
 

26. How to get the last day of the previous quarter


--Last day of the previous quarter
SELECT DATEADD (dd, -1,
           DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0))

--Last day of the previous quarter

SELECT DATEADD (dd, -1,

DATEADD(qq, DATEDIFF(qq, 0, GETDATE()), 0))

27. How to get the last day of the current quarter

--Last day of the current quarter
SELECT DATEADD (dd, -1,
           DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) +1, 0))



1
2
3
4
5

 
--Last day of the current quarter
SELECT DATEADD (dd, -1,
           DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) +1, 0))
 

28. How to get the last day of the next quarter

--Last day of the next quarter
SELECT DATEADD (dd, -1,
           DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) +2, 0))



1
2
3
4
5

 
--Last day of the next quarter
SELECT DATEADD (dd, -1,
           DATEADD(qq, DATEDIFF(qq, 0, GETDATE()) +2, 0))
 

29. How to get the last day of the quarter for any given date


DECLARE @date DATETIME = '07/28/2016'
SELECT DATEADD (dd, -1,DATEADD(qq, DATEDIFF(qq, 0, @date) +1, 0))

DECLARE @date DATETIME = '07/28/2016'

SELECT DATEADD (dd, -1,DATEADD(qq, DATEDIFF(qq, 0, @date) +1, 0))

30. How to get the first day of the previous month


--First day of the previous month
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)

--First day of the previous month

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) - 1, 0)

31. How to get the first day of the current month


--First day of the current month
SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

--First day of the current month

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)

32. How to get the first day of the next month

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)

1
2
3

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0)

33. How to get the first day of the month for any given date


--First day of the month for any given date
DECLARE @date DATETIME = '07/28/2016'
SELECT DATEADD(mm, DATEDIFF(mm, 0, @date), 0)

--First day of the month for any given date

DECLARE @date DATETIME = '07/28/2016'

SELECT DATEADD(mm, DATEDIFF(mm, 0, @date), 0)

34. How to get the last day of the previous month


--Last day of the previous month
SELECT DATEADD(dd, -1,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))

--Last day of the previous month

SELECT DATEADD(dd, -1,DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))

35. How to get the last day of the current month

--Last day of the current month
SELECT DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))

1
2
3
4

--Last day of the current month
SELECT DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 1, 0))

36. How to get the last day of the next month


--Last day of the next month
SELECT DATEADD(dd, -1,
           DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 2, 0))

--Last day of the next month

SELECT DATEADD(dd, -1,

DATEADD(mm, DATEDIFF(mm, 0, GETDATE()) + 2, 0))

37. How to get the last day of the month for any given date

--Last day of the month for any given date
DECLARE @date DATETIME = '07/28/2016'
SELECT DATEADD(dd, -1,
           DATEADD(mm, DATEDIFF(mm, 0, @date) + 1, 0))



1
2
3
4
5
6

 
--Last day of the month for any given date
DECLARE @date DATETIME = '07/28/2016'
SELECT DATEADD(dd, -1,
           DATEADD(mm, DATEDIFF(mm, 0, @date) + 1, 0))
 

38. How to get all tables in a Database

We can use sys.Tables catalog view to get the list of all the tables in a database. Here in the below script, we are trying to get the list of all the tables in the SqlhintsDemoDB database

USE SqlhintsDemoDB
GO
SELECT * FROM sys.tables



1
2
3
4
5

 
USE SqlhintsDemoDB
GO
SELECT * FROM sys.tables
 

39. How to get all stored procedures in a database

We can use sys.procedures catalog view to get the list of all the stored procedures in a database

SELECT * FROM sys.procedures

1
2
3

SELECT * FROM sys.procedures

40. How to get all functions in a database

We can use sys.objects catalog view as shown in the below script to get all the functions in a database. Here joining with sys.sql_modules to display the function definition.


SELECT o.Name, m.[Definition], o.type_desc
FROM sys.objects o
        INNER JOIN sys.sql_modules m
            ON m.object_id=o.object_id
WHERE o.type_desc like '%function%'

SELECT o.Name, m.[Definition], o.type_desc

FROM sys.objects o

INNER JOIN sys.sql_modules m

ON m.object_id=o.object_id

WHERE o.type_desc like '%function%'

41. How to check the definition or content of a stored procedure in SQL Server

We can use the system stored procedure sp_helptext to check the definition of a Stored Procedure in SQL Server. In the below example we are using the sp_helptext system stored procedure to check the definition of the Stored Procedure GetCityCustomers.

sp_helptext GetCityCustomers

1
2
3

sp_helptext GetCityCustomers

42. How to check if a record exists in a table in SQL Server

Below example script checks the existence of the customer record with CustId = 2 in the IF statement

DECLARE @CustId INT = 2
IF EXISTS(SELECT 1 FROM dbo.Customer WITH(NOLOCK)
          WHERE CustId = @CustId)
    BEGIN
        PRINT 'Record Exists'
    END
ELSE
    BEGIN
        PRINT 'Record doesn''t Exists'
    END



1
2
3
4
5
6
7
8
9
10
11
12

 
DECLARE @CustId INT = 2
IF EXISTS(SELECT 1 FROM dbo.Customer WITH(NOLOCK)
          WHERE CustId = @CustId)
    BEGIN
        PRINT 'Record Exists'
    END
ELSE
    BEGIN
        PRINT 'Record doesn''t Exists'
    END
 

43. How to rename column name in SQL Server

We can use the system stored procedure SP_RENAME to rename the table column. Below is the SYNTAX of the SP_RENAME system stored procedure for renaming the column name:


SP_RENAME 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'

SP_RENAME 'TableName.OldColumnName' , 'NewColumnName', 'COLUMN'

Example 1: Rename Customer table column CustName to FullName using SP_RENAME


SP_RENAME 'Customer.CustName' , 'FullName', 'COLUMN'

SP_RENAME 'Customer.CustName' , 'FullName', 'COLUMN'

44. How to get month name from date in SQL Server

We can use DATENAME() function to get Month name from Date in SQL Server, here we need to specify the datepart parameter of the DATENAME function as a month or mm or m all will return the same result.

SELECT GETDATE() 'Today', DATENAME(month,GETDATE()) 'Month Name'
SELECT GetDate() 'Today', DATENAME(mm,GETDATE()) 'Month Name'
SELECT GetDate() 'Today', DATENAME(m,GETDATE()) 'Month Name'



1
2
3
4
5

 
SELECT GETDATE() 'Today', DATENAME(month,GETDATE()) 'Month Name'
SELECT GetDate() 'Today', DATENAME(mm,GETDATE()) 'Month Name'
SELECT GetDate() 'Today', DATENAME(m,GETDATE()) 'Month Name'
 

45. How to get Day or Weekday name from Date in SQL Server

We can use DATENAME() function to get Day/Weekday name from Date in SQL Server, here we need to specify datepart parameter of the DATENAME function as weekday or dw both will return the same result.

SELECT GETDATE() 'Today', DATENAME(weekday,GETDATE()) 'Day Name'
SELECT GetDate() 'Today', DATENAME(dw,GETDATE()) 'Day Name'

1
2
3
4

SELECT GETDATE() 'Today', DATENAME(weekday,GETDATE()) 'Day Name'
SELECT GetDate() 'Today', DATENAME(dw,GETDATE()) 'Day Name'

46. How to find whether a Table is referenced by the Foreign Key constraint defined in another Table in SQL Server

We can use a script like below to identify whether a Table is referenced by another Tables foreign key constraints in SQL Server:

SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table',
 OBJECT_NAME(FK.parent_object_id) 'Referring Table',
 FK.name 'Foreign Key',
 COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column',
 COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column'
FROM sys.foreign_keys AS FK
        INNER JOIN sys.foreign_key_columns AS FKC
            ON FKC.constraint_object_id = FK.OBJECT_ID
WHERE OBJECT_NAME (FK.referenced_object_id) = 'Enter Table Name'



1
2
3
4
5
6
7
8
9
10
11

 
SELECT OBJECT_NAME (FK.referenced_object_id) 'Referenced Table',
 OBJECT_NAME(FK.parent_object_id) 'Referring Table',
 FK.name 'Foreign Key',
 COL_NAME(FK.referenced_object_id, FKC.referenced_column_id) 'Referenced Column',
 COL_NAME(FK.parent_object_id,FKC.parent_column_id) 'Referring Column'
FROM sys.foreign_keys AS FK
        INNER JOIN sys.foreign_key_columns AS FKC
            ON FKC.constraint_object_id = FK.OBJECT_ID
WHERE OBJECT_NAME (FK.referenced_object_id) = 'Enter Table Name'
 

47. How to Check if a String Contains a Sub-string in it in SQL Server

We can use the CHARINDEX() function to check whether a String contains a Sub-string in it. The name of this function is a little confusing as the name sounds something to do with character, but it returns the starting position of matched Substring in the main String. If it is not found then this function returns value 0. The below example demonstrates how we can use the CHARINDEX() function to check whether a String contains a Sub-string in it.

DECLARE @ExpressionToSearch VARCHAR(50)
SET @ExpressionToSearch = 'Basavaraj Prabhu Biradar'
--Check whether @ExpressionToSearch contains the substring
--'Prabhu' in it
IF  CHARINDEX('Prabhu', @ExpressionToSearch ) > 0
    PRINT 'Yes it Contains'
ELSE
    PRINT 'It doesn''t Contain'



1
2
3
4
5
6
7
8
9
10

 
DECLARE @ExpressionToSearch VARCHAR(50)
SET @ExpressionToSearch = 'Basavaraj Prabhu Biradar'
--Check whether @ExpressionToSearch contains the substring
--'Prabhu' in it
IF  CHARINDEX('Prabhu', @ExpressionToSearch ) > 0
    PRINT 'Yes it Contains'
ELSE
    PRINT 'It doesn''t Contain'
 

48. How to get all the records which contain double-byte data in a particular NVARCHAR data type column SQL Server

In NVARCHAR DataType column we can store both Single byte and Double byte data. Many times we want to know, how many records have double-byte data in the NVARCHAR data type column. We can write a script like below for this:


--Query to get all the customers whose CustomerName 
--column contains DOUBLE Byte Data
SELECT *
FROM dbo.Customer
WHERE CustomerName != CAST(CustomerName AS VARCHAR(50))

--Query to get all the customers whose CustomerName

--column contains DOUBLE Byte Data

SELECT *

FROM dbo.Customer

WHERE CustomerName != CAST(CustomerName AS VARCHAR(50))

49. How to get all the records which contain only single-byte data in SQL Server

In NVARCHAR DataType column we can store both Single byte and Double byte data. Many times we want to know, how many records have only single-byte data in the NVARCHAR data type column. We can write a script like below for this:

-- Query to get all the customers whose CustomerName
-- column contains SINGLE Byte Data only
SELECT *
FROM dbo.Customer
WHERE CustomerName = CAST(CustomerName AS VARCHAR(50))



1
2
3
4
5
6
7

 
-- Query to get all the customers whose CustomerName
-- column contains SINGLE Byte Data only
SELECT *
FROM dbo.Customer
WHERE CustomerName = CAST(CustomerName AS VARCHAR(50))
 

50. How to get Date Part only from DateTime in SQL Server

There are multiple ways of getting date part only from DateTime, below is one such approach.

SELECT CONVERT (DATE, GETDATE()) 'Date Part Only'

1
2
3

SELECT CONVERT (DATE, GETDATE()) 'Date Part Only'

I hope you liked this article and if any of your commonly used queries are not listed here, please let me know. I will post it and we can help together to the SQL Server Developers community.

Frequently Used Queries in SQL Server

The article was published on March 23, 2017 @ 9:08 AM

Frequently Used Queries in SQL Server – Part 1