In Database
1
634 views

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

SQL: A Brief

SQL is 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 database, tables, indexes, procedure 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 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 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 List of tables with 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 list of Stored procedure modified in 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 List of Stored procedures created in 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 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 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 given date in particular format or not in SQL Server

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

30. Find Odd rows from 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 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  size of the log for each database

DBCC SQLPERF (LOGSPACE);

36. Find what request are running on 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 which 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 queries 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…

Recent Posts
  • Neeraj Juneja

    Good job keep it up again!

We Are Here to Help!
Name:
Phone:
Email:
Country:
Message:
Captcha:
 

Start typing and press Enter to search