5/13/2016 50 Important Queries In SQL Server

In this article I will explain some general purpose 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

Example:

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

01. exec sp_helptext @objname = 'Object_Name'

Example:

[Link] 1/26
5/13/2016 50 Important Queries In SQL Server

Query 3: Get All Stored Procedure Relate To Database

01. SELECT DISTINCT [Link], [Link]

02.
03. FROM syscomments c
04.
05. INNER JOIN sysobjects o ON [Link]=[Link]
06.
07. WHERE [Link]='P'

Example:

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 [Link], [Link]

02.
03. FROM syscomments c
04.
05. INNER JOIN sysobjects o ON [Link]=[Link]
06.
07. WHERE [Link] LIKE '%Table_Name%' AND [Link]='P'

Example:

[Link] 2/26
5/13/2016 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 ('?', ' ', 80)"
02.
03. GO
04.
05. EXEC sp_updatestats
06.
07. GO

Example:

[Link] 3/26
5/13/2016 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.

Query:

01. ;WITH stored_procedures AS (

02.
03. SELECT
04.
05. [Link] AS table_name,
06.
07. ROW_NUMBER() OVER(partition by [Link],[Link] ORDER BY [Link],[Link]
08.
09. FROM sysdepends d
10.
11. INNER JOIN sysobjects o ON [Link]=[Link]
12.
13. INNER JOIN sysobjects oo ON [Link]=[Link]
14.
15. WHERE [Link] = 'P' AND [Link] LIKE '%SP_NAme%' )
16.
17. SELECT Table_name FROM stored_procedures
18.
19. WHERE row = 1

Example:

[Link] 4/26
5/13/2016 50 Important Queries In SQL Server

Query 7: Find Byte Size Of All tables in database

01. SELECT [Link] AS Table_Name,

02.
03. SUM([Link]) AS [Size_Table(Bytes)]
04.
05. FROM sysobjects sob, syscolumns sys
06.
07. WHERE [Link]='u' AND [Link]=[Link]
08.
09. GROUP BY [Link]

Example:

[Link] 5/26
5/13/2016 50 Important Queries In SQL Server

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

Query:

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=[Link]))
20.
21. AND
22.
23. TABLE_TYPE ='BASE TABLE'

Example:

[Link] 6/26
5/13/2016 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:

[Link] 7/26
5/13/2016 50 Important Queries In SQL Server

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 '

[Link] 8/26
5/13/2016 50 Important Queries In SQL Server
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

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_Records) SELECT '
12.
13. SELECT * FROM #Tab t ORDER BY t.Total_Records DESC;
14.
15. DROP TABLE #Tab;

Example:

[Link] 9/26
5/13/2016 50 Important Queries In SQL Server

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:

[Link] 10/26
5/13/2016 50 Important Queries In SQL Server

Query16: 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'

Example:

Query18: Get precision level used by decimal and numeric as current set in Server:

01. SELECT @@MAX_PRECISION AS 'MAX_PRECISION'

Example:

[Link] 11/26
5/13/2016 50 Important Queries In SQL Server

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:

[Link] 12/26
5/13/2016 50 Important Queries In SQL Server

Query22: 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:

[Link] 13/26
5/13/2016 50 Important Queries In SQL Server

Query24: Disable a Particular Trigger

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:

[Link] 14/26
5/13/2016 50 Important Queries In SQL Server

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.

Syntax:

01. Use Database_Name

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

Example:

Query29: Enable All Trigger for database

01. Use Demo

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

Example:

Query30: List of Stored procedure modified in last N days

01. SELECT name,modify_date

02.
03. FROM [Link]
04.
05. WHERE type='P'
06.
07. AND DATEDIFF(D,modify_date,GETDATE())< N

Example:

[Link] 15/26
5/13/2016 50 Important Queries In SQL Server

Query31: List of Stored procedure created in last N days

01. SELECT name,[Link].create_date

02.
03. FROM [Link]
04.
05. WHERE type='P'
06.
07. AND DATEDIFF(D,[Link].create_date,GETDATE())< N

Example:

Query32: Recompile a stored procedure

01. EXEC sp_recompile'Procedure_Name';

02.
[Link] 16/26
5/13/2016 50 Important Queries In SQL Server
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:

Query:

01. SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, [Link] as Column_Name

02.
03. FROM [Link] AS c
04.
05. JOIN [Link] AS t ON c.user_type_id=t.user_type_id
06.
07. WHERE [Link] = 'Data_Type'

Example:

[Link] 17/26
5/13/2016 50 Important Queries In SQL Server

Query 35: Get all Nullable columns of a table

01. SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, [Link] as Column_Name

02.
03. FROM [Link] AS c
04.
05. JOIN [Link] 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:

[Link] 18/26
5/13/2016 50 Important Queries In SQL Server

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

01. SELECT name AS Table_Name

02.
03. FROM [Link]
04.
05. WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
06.
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 [Link]
04.
05. WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasForeignKey') = 0
06.
07. ORDER BY Table_Name;

[Link] 19/26
5/13/2016 50 Important Queries In SQL Server

Example:

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

01. SELECT name AS Table_Name

02.
03. FROM [Link]
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:

[Link] 20/26
5/13/2016 50 Important Queries In SQL Server

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

01. SELECT CONVERT(VARCHAR(25),DATEADD(DAY,‐

(DAY(GETDATE())), DATEADD(MONTH,1,GETDATE())),105) Last_Date_Current_Month;

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_Month;

Example:

Query 43: Swap the values of two columns

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

[Link] 21/26
5/13/2016 50 Important Queries In SQL Server

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 [Link] 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

Example:

[Link] 22/26
5/13/2016 50 Important Queries In SQL Server

Query 45: Remove all views from database

01. Declare @Drop_View Nvarchar(MAX)

02.
03. Declare My_Cursor Cursor For Select [name] From [Link] 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:

[Link] 23/26
5/13/2016 50 Important Queries In SQL Server

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

01. SELECT TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

[Link] 24/26
5/13/2016 50 Important Queries In SQL Server

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:

[Link] 25/26
5/13/2016 50 Important Queries In SQL Server

Read more articles on SQL Queries:

Top 10 Most Used SQL Queries

Useful SQL Queries For SharePoint Practitioners

[Link] 26/26