50 Important Queries in SQL Server

Here let's learn 50 most important SQL queries in SQL Server. SQL SQL Server is one of the most popular RDBMs used by enterprizes and used by millions of applications. To become a good software and data developer, understanding SQL is very important. In SQL Server, all data is accessed and updated using SQL queries. In this article, I will discuss top 50 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.

Query 1: How to Retrieve List of All Databases in SQL Server

The following SQL query retrieves a list of all databases on a SQL Server.

EXEC sp_helpdb

Query 2: How to Display Text of a Stored Procedure, Trigger, or View in SQL Server

The following SQL query gets the display text of a stored procedure, trigger, or view on SQL Server.

exec sp_helptext @objname = 'Object_Name'

Query 3: How to List All Stored Procedures in SQL Server

The following SQL query gets a list of all stored procedures in a SQL Server database.

SELECT DISTINCT o.name, o.xtype  
  
FROM syscomments c  
  
INNER JOIN sysobjects o ON c.id=o.id  
  
WHERE o.xtype='P'

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

Query 4: How to Get All Stored Procedures Related To a Table in SQL Server

The following SQL query gets all stored procedures related to a database table in SQL Server.

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

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

Query 5: How to Rebuild All Indexes of a SQL Server Database

The following SQL query rebuilds all indexes of a SQL Server database.

EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"  
  
GO  
  
EXEC sp_updatestats  
  
GO

Query 6: How to Retrieve All dependencies of Stored Procedures in SQL Server

This query returns all object names that are being using in stored procedurs like tables, user define functions, another stored procedures and so on.

;WITH stored_procedures AS (  
  
SELECT  
  
oo.name AS table_name,  
  
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row  
  
FROM sysdepends d  
  
INNER JOIN sysobjects o ON o.id=d.id  
  
INNER JOIN sysobjects oo ON oo.id=d.depid  
  
WHERE o.xtype = 'P' AND o.name LIKE '%SP_NAme%' )  
  
SELECT Table_name FROM stored_procedures  
  
WHERE row = 1

Query 7: How to Find Size of a Database Table in SQL Server

The following SQL query gets the size of a database table of a SQL Server database.

SELECT sob.name AS Table_Name,  
  
SUM(sys.length) AS [Size_Table(Bytes)]  
  
FROM sysobjects sob, syscolumns sys  
  
WHERE sob.xtype='u' AND sys.id=sob.id  
  
GROUP BY sob.name

Query 8: How to get all tables that don’t have identity columns in SQL Server

The following SQL query gets all tables of a SQL Server database that don't have identity columns.

SELECT
TABLE_NAME FROM INFORMATION_SCHEMA.TABLES  
where
Table_NAME NOT IN
(
SELECT DISTINCT c.TABLE_NAME FROM INFORMATION_SCHEMA.COLUMNS c
INNER
JOIN sys.identity_columns ic
on
(c.COLUMN_NAME=ic.NAME))
AND
TABLE_TYPE ='BASE TABLE'  

Query 9: How to List Primary Keys and Foreign Keys in a SQL Server Database

The following SQL query gets a list of all primary keys and foreign keys on a SQL Server database.

SELECT
DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
GO

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

SELECT
DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME='Table_Name'
GO

Query 11: RESEED Identity of all tables

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

Query 12: List of tables with number of records

CREATE TABLE #Tab
(
Table_Name [varchar](max),
Total_Records int
);
EXEC sp_MSForEachTable @command1=' Insert Into #Tab(Table_Name, Total_Records) SELECT ''?'', COUNT(*) FROM ?'
SELECT * FROM #Tab t ORDER BY t.Total_Records DESC;
DROP TABLE #Tab;

Query 13: Get the version name of SQL Server

SELECT @@VERSION AS Version_Name 

Query 14: Get Current Language of SQL Server

SELECT @@LANGUAGE AS Current_Language;

Query 15: Disable all constraints of a table

ALTER TABLE Table_Name NOCHECK CONSTRAINT ALL  

Query 16: Disable all constraints of all tables

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL' 

Query 17: Get Current Language Id

SELECT @@LANGID AS 'Language ID'  

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

SELECT @@MAX_PRECISION AS 'MAX_PRECISION'  

Query 19: Return Server Name of SQL Server

SELECT @@SERVERNAME AS 'Server_Name'  

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