Frequently Used SQL Server Queries - Loginworks Softwares

Before starting with the detailed information about queries related to the SQL Server, it is important to have a brief understanding of what is it all about.

Jump to Section

SQL: A Brief Introduction

SQL stands for Structured Query Language, which is often referred to as a computer language for storing, manipulating, and retrieving data stored in a relational database.

It is regarded as the standard language for Relational Database System. All the Relational Database Management Systems (RDMS) like MySQL, MS Access, Oracle, Sybase, Informix, Postgres, and SQL Server use SQL as their standard database language.

I am sure after this brief knowledge about the topic, you will be able to digest its further processing easily. In this article, I am trying to list out some frequently used SQL Server Queries related to databases, tables, indexes, procedures, and functions.

1. Check existence of database in SQL Server

use DB_ID() function
 Example:
 IF DB_ID('Student') IS NOT NULL
   BEGIN
   -- write your logic here
   END

2. Check existence of table in the database in SQL Server

use sys.Tables
 Example:
 IF EXISTS(Select null from sys.Tables where Name = 'Account')
   BEGIN
   -- write your logic here
   END

3. Check existence of Stored Procedure in SQL Server

use sys.procedures
Example:
 IF EXISTS(SELECT null FROM sys.procedures WHERE Name ='sp_Student_Select')
 BEGIN
 -- write your logic here
 END

4. Check existence of Function in SQL Server

 use sys.objects
 Example:
 IF EXISTS (SELECT null FROM sys.objects WHERE Name ='GetSalary')
 BEGIN
 -- write your logic here
 END

5. Create an index on Table in SQL Server

CREATE INDEX IndexName ON TableName(Column1,Column2);

6. Delete duplicate rows from Table in SQL Server

By Using Row_Number
Example:
 WITH AccountTemp (Name,duplicateCount)AS
 (SELECT Name,ROW_NUMBER() OVER(PARTITION by Name ORDER BY Name) AS duplicateCount FROM Account)
 --Now delete records from TempTable
 Delete from AccountTemp WHERE duplicateCount > 1

7. Find Tables without index in SQL Server

SELECT Name as 'TableName' FROM SYS.tables WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasIndex')=0

8. Find Tables without Primary Key in SQL Server

SELECT Name as 'TableName' FROM SYS.Tables 
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey')= 0 AND type= 'U'

9. Find Specific text in Stored Procedure in SQL Server

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

10. Find tables that have specified column name in SQL Server

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

11. List down all Database in SQL Server

sp_helpdb

12. Get All Stored Procedure present in all Database in SQL Server

SELECT DISTINCT b.name FROM syscomments co INNER JOIN sysobjects b ON co.id=b.id  
WHERE b.xtype='P'

13. Get All Stored Procedure Related To Table in SQL Server

SELECT DISTINCT o.name FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id 
WHERE c.TEXT LIKE '%Table_Name%' AND o.xtype='P'

14. RESEED Identity of all tables in SQL Server

EXEC sp_MSForEachTable'
 IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
 DBCC CHECKIDENT (''?'', RESEED, 1)'

15. Select a List of tables with a number of records

SELECT o.NAME 'Table Name',i.rowcnt 'No of Rows'
FROM sysindexes AS I INNER JOIN sysobjects AS o ON i.id = o.id
WHERE i.indid < 2 AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
ORDER BY o.NAME

16. Get Current Language of SQL Server

SELECT @@LANGUAGE AS CurLang;

17. Disable all constraints of a table in SQL Server

ALTER TABLE TableName 
NOCHECK CONSTRAINT ALL

18. Enable all constraints of a table in SQL Server

ALTER TABLE TableName 
CHECK CONSTRAINT ALL

19. Find a list of Stored procedure modified in the last N days in SQL Server

SELECT name,modify_date 
FROM sys.objects
WHERE type='P' AND DATEDIFF(D,modify_date,GETDATE())<'N'

20. Find a List of Stored procedures created in the last N days in SQL Server

SELECT name, create_date 
FROM sys.objects
WHERE type='P' AND DATEDIFF(D,modify_date,GETDATE())<'N'

21. Get all columns of a specific data type in SQL Server

SELECT OBJECT_NAME(col.OBJECT_ID) as TableName,col.name as ColumnName
FROM sys.columns AS col JOIN sys.types AS tp ON col.user_type_id=tp.user_type_id
WHERE tp.name ='DataType';

22. Get all Nullable columns of a table in SQL Server

SELECT OBJECT_NAME(col.OBJECT_ID) as TableName, col.name as ColumnName
FROM sys.columns AS col JOIN sys.types AS tp ON col.user_type_id=tp.user_type_id
WHERE col.is_nullable=0 AND OBJECT_NAME(col.OBJECT_ID)='Table_Name';

23. Get the First Date of Current Month in SQL Server

SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE()))+1,GETDATE()),105) FirstDate;

24. Select first 3 characters in String in SQL Server

SELECT substring('India',1,3);

25. Find Position in String in SQL Server

SELECT CHARINDEX( 'My', 'My India', 1);

26. Get First Name, Year, Month and Date of joining year of an employee in SQL Server

Select SUBSTRING (convert(varchar,joiningdate,103),7,4) , SUBSTRING
(convert(varchar,joiningdate,100),1,3), SUBSTRING(convert(varchar,joiningdate,100),5,2) 
from Emp;

27. Find Second highest salary from Employee table in SQL Server

SELECT MAX(Sal) FROM Emp WHERE Sal NOT IN (select MAX(Sal) from Emp;

28. Find Maximum Salary from each department in SQL Server

SELECT DepartmentID,MAX(Sal) FROM Emp GROUP BY DepartmentID;

29. Check Whether the given date is in a particular format or not in SQL Server

SELECT ISDATE('01/24/17') AS 'MM/DD/YY';

30. Find Odd rows from a table

SELECT E.BrandId, E.Brand FROM (
SELECT *, Row_Number() OVER(ORDER BY BrandId) AS RowNumber FROM Brand) E
WHERE E.RowNumber % 2 = 1

31. Find even rows from a table

SELECT E.BrandId, E.Brand
FROM (SELECT *, Row_Number() OVER(ORDER BY BrandId) AS RowNumber FROM Brand) 
E WHERE E.RowNumber % 2 = 1

32. Find Most used tables In SQL Server

SELECT db_name(ius.database_id) AS DatabaseName, tbl.NAME AS TableName,
SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS NbrTimesAccessed
FROM sys.dm_db_index_usage_stats ius INNER JOIN sys.tables tbl ON tbl.OBJECT_ID = ius.object_id
WHERE database_id = DB_ID('DV_Migration') GROUP BY database_id,tbl.name
ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC

33. Find most-used indexes In SQL Server

SELECT db_name(ius.database_id) AS DatabaseName,
 tbl.NAME AS TableName,i.NAME AS IndexName,i.type_desc AS IndexType,
 ius.user_seeks + ius.user_scans + ius.user_lookups AS NbrTimesAccessed
 FROM sys.dm_db_index_usage_stats ius
 INNER JOIN sys.indexes i ON i.OBJECT_ID = ius.OBJECT_ID AND i.index_id = ius.index_id
 INNER JOIN sys.tables tbl ON tbl.OBJECT_ID = i.object_id
 WHERE database_id = DB_ID('DV_Migration')
 ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC

34. Find Most frequently run queries in SQL Server

SELECT [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt ORDER BY qs.execution_count desc;

35. Find the size of the log for each database

DBCC SQLPERF (LOGSPACE);

36. Find what request is running on the server

SELECT SUBSTRING(dest.text, ( deqs.statement_start_offset / 2 ) + 1,
( CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
ELSE deqs.statement_end_offset- deqs.statement_start_offset
END ) / 2 + 1) AS querystatement ,deqp.query_plan ,deqs.execution_count ,deqs.total_worker_time ,deqs.total_logical_reads ,deqs.total_elapsed_time FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp;

37. Find disk space of individual objects, tables, and indexes in SQL server

EXEC sys.sp_spaceused @objname = N'Sales.SalesOrderHeader';

38. Finding duplicate records in Table in SQL Server

SELECT Name, Count(Name) FROM TableName GROUP BY Name
HAVING Count(Name)>1

39. Query for Displaying Primary Keys in SQL Server

SELECT * FROM Sys.Objects WHERE Type='PK'

Reference Links

The queries stated above are the common queries that are frequently faced on the developer’s end. For more information, you can go through these URLs.

http://sqlhints.com/tag/common-queries-in-sql/

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/ee6cb272-042b-4640-8aed-27a9b92c4555/query-to-find-most-frequently-used-objects-tables-indexes-etc?forum=transactsql

http://sqlhints.com/2016/08/28/100-frequently-used-queries-in-sql-server-part-1/

http://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/

Summing Up

I think every software programmer should be equipped with the knowledge of the above-mentioned queries. I hope my query collection will be able to help you in tackling most of the complex tasks of software programmers. In case of any other queries, you can surely post your valuable comments in the comments section below…

About
Latest Posts

Follow me!

Imran Saifi

Database Administrator at Loginworks Softwares

I am a database administrator who has the responsibility of maintaining the integrity and performance of company databases. I guarantee that the data is stored securely and ensure all the users are informed of any changes in the same. Apart from managing all this bulk of technological binaries, I love to enjoy little things in my life. I am an avid foodie who loves to explore the abundance of specialties our cultures offer. I am also into sports, especially cricket. I make sure that I never miss my weekend morning cricket matches alongside my long-time friends. I cherish both my professional and personal lives to the fullest.