10/22/21, 5:48 PM 50 Important Queries In SQL Server

Windows 11 is Here. Read what is new in Windows 11. x

PRODUCTIVITY: Top 10 Tips To Increase Productivity

Become a member
C# Corner Login
Post Ask Question

50 Important Queries In SQL Server

Pankaj Kumar Choudhary Updated date Nov 28, 2018

774.7k 104 81

Download Free .NET & JAVA Files API

Try Free File Format APIs for Word/Excel/PDF

In this article I will explain some general purpose SQL queries. I think each developer should
have knowledge of these queries. These queries are not related to any specific topic of SQL.
But knowledge of such queries can solve some complex tasks and may be used in many
scenarios, so I decided to write an article on these queries.

Query 1: Retrieve List of All Database

01. EXEC sp_helpdb  

Query 2: Display Text of Stored Procedure, Trigger, View

01. exec sp_helptext @objname = 'Object_Name'  
https://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/ 1/19
10/22/21, 5:48 PM 50 Important Queries In SQL Server

Query 3: Get All Stored Procedure Relate To Database

01. SELECT DISTINCT o.name, o.xtype  
02.   
03. FROM syscomments c  
04.   
05. INNER JOIN sysobjects o ON c.id=o.id  
06.   
07. WHERE o.xtype='P'  

https://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/ 2/19
10/22/21, 5:48 PM 50 Important Queries In SQL Server

To retrieve the View use “V” instead of “P” and for functions use “FN.

Query 4: Get All Stored Procedure Relate To Table

01. SELECT DISTINCT o.name, o.xtype  
02.   
03. FROM syscomments c  
04.   
05. INNER JOIN sysobjects o ON c.id=o.id  
06.   
07. WHERE c.TEXT LIKE '%Table_Name%' AND o.xtype='P'  

https://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/ 3/19
10/22/21, 5:48 PM 50 Important Queries In SQL Server

To retrieve the View use “V” instead of “P” and for functions use “FN.

Query 5: Rebuild All Index of Database

01. EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 8
02.   
03. GO  
04.   
05. EXEC sp_updatestats  
06.   
07. GO

https://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/ 4/19
10/22/21, 5:48 PM 50 Important Queries In SQL Server

Query 6: Retrieve All dependencies of Stored Procedure

This query return all objects name that are using into stored procedure like table, user
define function, another stored procedure.
01. ;WITH stored_procedures AS (  
02.   
03. SELECT  
04.   
05. oo.name AS table_name,  
06.   
07. ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) 
08.   
09. FROM sysdepends d  
10.   
11. INNER JOIN sysobjects o ON o.id=d.id  
12.   
13. INNER JOIN sysobjects oo ON oo.id=d.depid  
14.   
15. WHERE o.xtype = 'P' AND o.name LIKE '%SP_NAme%' )  
16.   
17. SELECT Table_name FROM stored_procedures  
18.   
19. WHERE row = 1  

https://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/ 5/19
10/22/21, 5:48 PM 50 Important Queries In SQL Server

Query 7: Find Byte Size Of All tables in database

01. SELECT sob.name AS Table_Name,  
02.   
03. SUM(sys.length) AS [Size_Table(Bytes)]  
04.   
05. FROM sysobjects sob, syscolumns sys  
06.   
07. WHERE sob.xtype='u' AND sys.id=sob.id  
08.   
09. GROUP BY sob.name

https://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/ 6/19
10/22/21, 5:48 PM 50 Important Queries In SQL Server

Query 8: Get all table that don’t have identity column

01. SELECT  
02.   
03. TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  
04.   
05. where  
06.   
07. Table_NAME NOT IN  
08.   
09. (  
10.   
11. SELECT DISTINCT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c  
12.   
13. INNER  
14.   
15. JOIN sys.identity_columns ic  
16.   
17. on  
18.   
19. (c.COLUMN_NAME=ic.NAME))  
20.   
21. AND  
22.   
23. TABLE_TYPE ='BASE TABLE'  

Example

https://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/ 7/19
10/22/21, 5:48 PM 50 Important Queries In SQL Server

Query 9: List of Primary Key and Foreign Key for Whole Database

01. SELECT  
02.   
03. DISTINCT  
04.   
05. Constraint_Name AS [Constraint],  
06.   
07. Table_Schema AS [Schema],  
08.   
09. Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  
10.   
11. GO

Example

Query 10: List of Primary Key and Foreign Key for a particular
table

01. SELECT  
02.   
03. DISTINCT  
04.   
05. Constraint_Name AS [Constraint],  
06.   
07. Table_Schema AS [Schema],  
08.   
09. Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE  
10.   
11. WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='Table_Name'  
12.   
13. GO

Example

Query 11: RESEED Identity of all tables

01. EXEC sp_MSForEachTable '  
02.   
03. IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1  
04.   
05. DBCC CHECKIDENT (''?'', RESEED, 0) 

Example

Query 12: List of tables with number of records

01. CREATE TABLE #Tab  
https://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/ 8/19
10/22/21, 5:48 PM 50 Important Queries In SQL Server

02.   
03. (  
04.   
05. Table_Name [varchar](max),  
06.   
07. Total_Records int  
08.   
09. );  
10.   
11. EXEC sp_MSForEachTable @command1=' Insert Into #Tab(Table_Name, Total_R
12.   
13. SELECT * FROM #Tab t ORDER BY t.Total_Records DESC;  
14.   
15. DROP TABLE #Tab;  

Example

Query 13: Get the version name of SQL Server

01. SELECT @@VERSION AS Version_Name  

Example

Query 14: Get Current Language of SQL Server

01. SELECT @@LANGUAGE AS Current_Language;  

Example

Query 15: Disable all constraints of a table

01. ALTER TABLE Table_Name NOCHECK CONSTRAINT ALL  

Example

Query 16: Disable all constraints of all tables

01. EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  

Example

Query 17: Get Current Language Id

01. SELECT @@LANGID AS 'Language ID'  

https://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/ 9/19
10/22/21, 5:48 PM 50 Important Queries In SQL Server

Example

Query 18: Get precision level used by decimal and numeric as

current set in Server

01. SELECT @@MAX_PRECISION AS 'MAX_PRECISION'  

Example

Query 19: Return Server Name of SQL Server

01. SELECT @@SERVERNAME AS 'Server_Name'  

Example

Query 20: Get name of register key under which SQL Server is
running

01. SELECT @@SERVICENAME AS 'Service_Name'

Example

Query 21: Get Session Id of current user process

01. SELECT @@SPID AS 'Session_Id'

Example

Query 22: Get Current Value of TEXTSIZE option

01. SELECT @@TEXTSIZE AS 'Text_Size'  

Example

Query 23: Retrieve Free Space of Hard Disk

01. EXEC master..xp_fixeddrives  

example

Query 24: Disable a Particular Trigger

https://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/ 10/19
10/22/21, 5:48 PM 50 Important Queries In SQL Server

Syntax
01. ALTER TABLE Table_Name DISABLE TRIGGER Trigger_Name  

Example

01. ALTER TABLE Employee DISABLE TRIGGER TR_Insert_Salary  

Query 25: Enable a Particular Trigger

Syntax
01. ALTER TABLE Table_Name ENABLE TRIGGER Trigger_Name  

Example

01. ALTER TABLE Employee ENABLE TRIGGER TR_Insert_Salary  

Query 26: Disable All Trigger of a table

We can disable and enable all triggers of a table using previous query, but replacing the
"ALL" instead of trigger name.

Syntax
01. ALTER TABLE Table_Name DISABLE TRIGGER ALL  

Example

01. ALTER TABLE Demo DISABLE TRIGGER ALL  

Query 27: Enable All Trigger of a table

01. ALTER TABLE Table_Name ENABLE TRIGGER ALL  

Example

01. ALTER TABLE Demo ENABLE TRIGGER ALL  

Query 28: Disable All Trigger for database

Using sp_msforeachtable system stored procedure we enable and disable all triggers for a
database.

https://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/ 11/19
10/22/21, 5:48 PM 50 Important Queries In SQL Server

Syntax

01. Use Database_Name  
02.   
03. Exec sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"  

Example

example

Query 29: Enable All Trigger for database

01. Use Demo  
02.   
03. Exec sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"  

example

Query 30: List of Stored procedure modified in last N days

01. SELECT name,modify_date  
02.   
03. FROM sys.objects  
04.   
05. WHERE type='P'  
06.   
07. AND DATEDIFF(D,modify_date,GETDATE())< N  

example

Query 31: List of Stored procedure created in last N days

01. SELECT name,sys.objects.create_date  
02.   
03. FROM sys.objects  
04.   
05. WHERE type='P'  
06.   
07. AND DATEDIFF(D,sys.objects.create_date,GETDATE())< N  

Example

Query 32: Recompile a stored procedure

https://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/ 12/19
10/22/21, 5:48 PM 50 Important Queries In SQL Server

01. EXEC sp_recompile'Procedure_Name';  
02.   
03. GO  

Example

Query 33: Recompile all stored procedure on a table

01. EXEC sp_recompile N'Table_Name';  
02.   
03. GO  

Example

Query 34: Get all columns of a specific data type

01. SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, c.name as Column_Name  
02.   
03. FROM sys.columns AS c  
04.   
05. JOIN sys.types AS t ON c.user_type_id=t.user_type_id  
06.   
07. WHERE t.name = 'Data_Type'  

Example

Query 35: Get all Nullable columns of a table

01. SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, c.name as Column_Name  
02.   
03. FROM sys.columns AS c  
04.   
05. JOIN sys.types AS t ON c.user_type_id=t.user_type_id  
06.   
07. WHERE c.is_nullable=0 AND OBJECT_NAME(c.OBJECT_ID)='Table_Name'  

Example

Query 36: Get All table that don’t have primary key

01. SELECT name AS Table_Name  
02.   
03. FROM sys.tables  
04.   
05. WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0  
06.   

https://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/ 13/19
10/22/21, 5:48 PM 50 Important Queries In SQL Server

07. ORDER BY Table_Name;  

Example

Query 37: Get All table that don’t have foreign key

01. SELECT name AS Table_Name  
02.   
03. FROM sys.tables  
04.   
05. WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasForeignKey') = 0  
06.   
07. ORDER BY Table_Name;  

Example

Query 38: Get All table that don’t have identity column

01. SELECT name AS Table_Name  
02.   
03. FROM sys.tables  
04.   
05. WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasIdentity') = 0  
06.   
07. ORDER BY Table_Name;  

Example

Query 39: Get First Date of Current Month

01. SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-
(DAY(GETDATE()))+1,GETDATE()),105) First_Date_Current_Month;  

Example

Query 40: Get last date of previous month

01. SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-
(DAY(GETDATE())),GETDATE()),105) Last_Date_Previous_Month;  

Example

Query 41: Get last date of current month

https://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/ 14/19
10/22/21, 5:48 PM 50 Important Queries In SQL Server

01. SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-
(DAY(GETDATE())), DATEADD(MONTH,1,GETDATE())),105) Last_Date_Current_Mo

Example

Query 42: Get first date of next month

01. SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-
(DAY(GETDATE())), DATEADD(MONTH,1,GETDATE())+1),105) First_Date_Next_Mo

Example

Query 43: Swap the values of two columns

01. UPDATE Table_Name SET Column1=Column2, Column2=Column1  

Example

Query 44: Remove all stored procedure from database

01. Declare @Drop_SP Nvarchar(MAX)  
02.   
03. Declare My_Cursor Cursor For Select [name] From sys.objects where type 
04.   
05. Open My_Cursor  
06.   
07. Fetch Next From My_Cursor Into @Drop_SP  
08.   
09. While @@FETCH_STATUS= 0  
10.   
11. Begin  
12.   
13. Exec('DROP PROCEDURE ' + @Drop_SP)  
14.   
15. Fetch Next From My_Cursor Into @Drop_SP  
16.   
17. End  
18.   
19. Close My_Cursor  
20.   
21. Deallocate My_Cursor  

https://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/ 15/19
10/22/21, 5:48 PM 50 Important Queries In SQL Server

Example

Query 45: Remove all views from database

01. Declare @Drop_View Nvarchar(MAX)  
02.   
03. Declare My_Cursor Cursor For Select [name] From sys.objects where type 
04.   
05. Open My_Cursor  
06.   
07. Fetch Next From My_Cursor Into @Drop_View  
08.   
09. While @@FETCH_STATUS = 0  
10.   
11. Begin  
12.   
13. Exec('DROP VIEW ' + @Drop_View)  
14.   
15. Fetch Next From My_Cursor Into @Drop_View  
16.   
17. End  
18.   
19. Close My_Cursor  
20.   
21. Deallocate My_Cursor  

Example

Query 46: Drop all tables

01. EXEC sys.sp_MSforeachtable @command1 = 'Drop Table ?'  

Example

Query 47: Get information of tables’ columns

01. SELECT * FROM INFORMATION_SCHEMA.COLUMNS  
02.   
03. WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME=’Table_Name’  

Example

Query 48: Get all columns contain any constraints

https://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/ 16/19
10/22/21, 5:48 PM 50 Important Queries In SQL Server
01. SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME FROM INFORMATION_SCHEMA.C

Example

Query 49: Get all tables that contain a view

01. SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE  

Example

Query 50: Get all columns of table that using in views

01. SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE  

Example

Read more articles on SQL Queries:

Top 10 Most Used SQL Queries
Useful SQL Queries For SharePoint Practitioners

50 Important Queries SQL Queries SQL Server

Next Recommended Reading

Top 30 Important SQL Queries For Developers

OUR BOOKS

https://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/ 17/19
10/22/21, 5:48 PM 50 Important Queries In SQL Server

Pankaj Kumar Choudhary

Pankaj Kumar Choudhary loves the Microsoft and Database technologies. He has worked
experience on several database technology like SQL Server, MySQL, Oracle, MongoDB,
PostgreSQL . He has knowledge of several technology ... Read more
https://www.c-sharpcorner.com/members/pankaj-kumar-choudhary
67 10.3m 3

View Previous Comments

81 104

Type your comment here and press Enter Key (Minimum 10 characters)

Please change Query 37 c.isnullable = 1

boopathi s Dec 30, 2020
2037 22 0 0 0 Reply

Nice information.Thanx for sharing.

Nilofers Shaikh Oct 27, 2017
2017 42 8k 4 0 Reply

Awesome article very helpful

Ravindar Kumar Sep 11, 2017
1985 74 0 4 0 Reply

Thank you sir ji for sharing

Ankur Verma Jan 07, 2017
1866 193 59 5 0 Reply

Nice article.query 3 should be above to query 2.

jitendra mishra Dec 02, 2016
2056 3 0 4 0 Reply

Awesome article it covers so many different scenarios

Bhanu Korremula Nov 30, 2016
1406 663 42.4k 4 0 Reply

Sp_spaceused tablename returns no. of rows in a table and size of table

Manish Kumar Nov 03, 2016
987 1.4k 6.2k 5 1 Reply

Thanks for share

Subash Nov 03, 2016
378 5.9k 66.5k 4

Thanks for sharing with us

Srikanth Agolla Sep 29, 2016
1960 99 4.5k 4 0 Reply

https://www.c-sharpcorner.com/article/50-important-queries-in-sql-server/ 18/19
10/22/21, 5:48 PM 50 Important Queries In SQL Server

Thanks to all of you....

Pankaj Kumar Choudhary Sep 26, 2016
67 26.8k 10.3m 4 0 Reply

Very helpful
Pawan Tiwari Sep 26, 2016
1269 835 334.1k 5 0 Reply

FEATURED ARTICLES
How To Upgrade to Windows 11
Exploring Subject <T> In Reactive Extensions For .Net
Micro Frontends With Webpack
What’s New In iPhone 13
Understanding Synchronization Context Task.ConfigureAwait In Action
View All

TRENDING UP

01 Getting Started With ASP.NET Web API .NET 5

Everything That Every .NET Developer Needs To Know About Disposable Types -
02 Properly Disposing Objects
03 How To Upgrade ASP.NET Core 2.1 To ASP.NET Core 3.1 version

04 What Happens Behind The Scenes When You Trade A Bitcoin?

05 Developing A Web App Using Angular 12, ASP.NET Core Web API And SQL Server

06 Build A ToDo List Application Using Angular, .Net 5.0 Web API And Microsoft SQL Server

07 Need For Site Reliability Engineering

08 Micro Frontends With Webpack

09 Two Ways To Create Minimal APIs In .NET 6

10 Generate Image Thumbnail using Azure Computer Vision and Function App
View All

About Us Contact Us Privacy Policy Terms Media Kit Sitemap Report a Bug FAQ Partners
C# Tutorials Common Interview Questions Stories Consultants Ideas Certifications
©2021 C# Corner. All contents are copyright of their authors.

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