SQL Server Queries | Most useful SQL Server Queries for Professionals

SQL Server Queries :

In my previous article i have given some complex sql queries which are useful for the interview purpose.In this article i would like to explain some most important and most used SQL Server Queries. These SQL server queries are really very important for real world. These are all real world examples of SQL Server  Queries.In this article i will focus on SQL server so that people will get idea about the SQL Server Queries.

Following are the most useful and important SQL Server Queries :

Question 1 :  How to check all databases in SQL Server?

Query :

EXEC sp_helpdb;

Question 2 : How to create table with primary key in SQL server?

Syntax :

Create table tablename

(

column_name datatype(size) PRIMARY KEY,

column_name1 datatype(size),

column_name1 datatype(size));

Example :

Create table Employee

(

RollNumber Number(10) PRIMARY KEY,

name varchar2(30));

Question 3 : How to create the view in SQL server
Syntax :
Create view Viewname
 as
Select
column1,column2….column..n;
Example :
Create view V_test
as
Select * from
Employee;
Question 4 : How to get all the column names from the table name in SQL server.
Query :
Select Column_name from Information_schema.Columns
Where table_name= ‘Tablename’;
The SQL server in built table named Information_schema.Columns used to fetch the column names from SQL server.
Question 5 : How to fetch all tables created by user.
Query :
Select Name from Sys.objects where Type=’U’;
Question 6 :How to fetch all view names from the database.
Query :
The Sys.views is the table which gives you information about all the views in database in SQL server.
Select * from Sys.Views;
Question 7 : How to create procedure in SQL Server.
Syntax :
Create procedure Procedurename
As
Select …….
……..Statements;
Example :
Create procedure P_test
as
select name,rollno from
Employee;
end;
Question 8 : How to check the Code of Stored procedure or function or trigger.
Syntax :
exec sp_helptext @objname = ‘Object_Name’ ;
Example : 
exec sp_helptext @objname = ‘P_test’ ;
Output :
Create procedure P_test
as
select name,rollno from
Employee;
end;
Question 9 : How to search column name from the database in SQL server.
Query :
Select c.name As ColName,t.name As Tablename
From sys.columns c
Join sys.tables t ON
c.object_id = t.object_id
where c.name  LIKE ‘%Columnname_Want_to_search%’;
Question 10 : How to get all the created stored procedure names from database in SQL server.
Query :
The sys.procedures table is used to fetch all procedure information in SQL Server.
Select * from Sys.Procedures;
Question 11 : How to check all procedure names related to specific database.
Query :
SELECT DISTINCT o.name, o.xtype
FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id WHERE o.xtype=‘P’ ;
Question 12 : How to check all the procedure names related to table.
Query :
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’;
Question 13 : Counting the table name and its count of rows in SQL server.
Query :
Select Tablename =  objects.name,rowcount = max(indexs.rows)
From Sysobjects objects , Sysindexes indexs
where objects.xtype = ‘U’
AND
indexes.id= OBJECT_ID(Objects.name)
Group by Objects.name Order by 2 DESC;

Question 14 : Rebuilding all indexes of tables in database.

Query :

EXEC sp_MSforeachtable @command1=“print ‘?’ DBCC DBREINDEX (‘?’, ‘ ‘, 80)”

GO

EXEC sp_updatestats

GO

Question 15 : How to check default language of SQL Server.

Query :

Select @@language AS DefaultLanguage;

Question 16 : How to check the Server name.

Query :

Select @@SERVERNAME AS ServerName;

Question 17 : Checking the SQL Server Procedure Dependencies.

Query :

This is most important as well as most used query in sql server.There is always need to check the dependencies of stored procedure.The following query gives you all the dependencies

WITH stored_procedures AS (

SELECT  objects.name AS table_name,

ROW_NUMBER() OVER(partition by o.name,objects.name ORDER BY o.name,objects.nameAS row

FROM sysdepends depends  INNER JOIN sysobjects o ON o.id=depends.id

INNER JOIN sysobjects objects ON objects.id=depends.depid

WHERE o.xtype = ‘P’ AND o.name LIKE ‘%StoredProcedurename%’ )

SELECT Table_name FROM stored_procedures WHERE row = 1;

Question 18 : How to get the list of tables without primary key.

Query :

Select Schema_name(Schema_id) As SchemaName, Name as Tablename

from sys.tables

where Objectproperty(Object_id,’TableHasPrimaryKey’)= 0

Order by SchemaName,TableName;

Question 19 : How to check the size of table from the database.

Query :

SELECT Objects.name AS Table_Name,

SUM(columns.length) AS [Size_Table(Bytes)]

FROM sysobjects Objects, syscolumns columns

WHERE Objects.xtype=‘u’ AND columns.id=Objects.id

GROUP BY Objects.name;

Question 20 : Some Important Date Queries.

20.1. How to get the name of current month in SQL server.

Select DATENAME(MONTH,GETDATE90) As Current_Month;

20.2. How to get the name of current day in SQL server.

Select DATENAME(WEEKDAY,GETDATE90) As Current_Day;

20.3. Get First and Last Date of Current Month.

First Date of Current Month :

Select Convert(varchar(25),Dateadd(Day,-(Day(GETDATE()))+1,GETDATE()),105) FirstDate;

Last Date of Current Month :

SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE())), DATEADD(MONTH,1,GETDATE())),105) LastDate;

Question 21 : How to get all tables which does not have identity column.

Query :

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

Question 22 : Drop all tables from SQL Server Database.

Query :

Exec sys.sp_MSforeachtable @command1=’Drop Table ?’;

Question 23 : List table name with its primary key and foreign keys from database.

Query :

This is most important query to check all the primary keys and foreign key from database. There are so many times where user wants to check the primary key and foreign key information of database which is used for data modeling. At that time the following query is important :

SELECT

DISTINCT

Constraint_Name AS [Constraint],

Table_Schema AS [Schema],

Table_Name AS [TableName] FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE

GO

Question 24 : How to rename database in SQL Server.

Query :

Exec sp_renamedb ‘Oldname’ , ‘NewName’;

ALTER DATABASE oldname Modify Name= New_Name;

SQL Server Queries

Question 25 : How to Rename tablename in SQL Server.

Query :

Exec sp_rename ‘Old_Table_Name’,’New_Table_Name’;

Question 26 : How to Rename the column name in SQL Server.

Query :

EXEC SP_RENAME ‘TableName.Old_Column_Name’ , ‘New_Column_Name’,’Column’;

Question 27 : How to check version name of SQL Server.

Query :

Select @@VERSION  As Version_Name;

Question 28 : How to Disable all constraints of table.

Query :

Alter Table Table_Name NOCHECK CONSTRAINT ALL;

Question 29 : How to disable all constraints of all tables in SQL Server.

Query :

This is most important SQL server query. There are so many times where user needs to disable all the constraints of all tables.

EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’;

Question 30 : How to check the Maximum precision of float type in SQL Server.

Query :

SELECT @@MAX_PRECISION AS ‘MAX_PRECISION’ ;

Question 31 : How to check session id of current session in SQL Server.

Query :

SELECT @@SPID AS ‘Session_Id’;

 

Question 32 : How to make database online as well as offline.

Query :

Make Database Online :

ALTER DATABASE SET ONLINE;

Make Database Offline :

ALTER DATABASE SET OFFLINE;

Question 33 : How to take backup as well as restore the database in MS SQL.

Query :

Taking backup of database :

BACKUP DATABASE DataBaseName TO DISK=’c:\Backup_File_Name.bak’;

Restoring Backup of database :

RESTORE DATABASE DataBaseName TO DISK=’c:\Backup_File_Name.bak’;

Question 34 : How to Retrieve free space from Hard Disk.

Query :

EXEC master..xp_fixeddrives;

Query 35 : How to fetch the current value of textsize.

Query :

SELECT @@TEXTSIZE AS ‘Text_Size’ ;

Question 36 : Enable and Disable all triggers in database .

Query :

Disable Triggers :

Use DatabaseName

Exec sp_msforeachtable “ALTER TABLE ? DISABLE TRIGGER all”

Enable Triggers :

Use DatabaseName

Exec sp_msforeachtable “ALTER TABLE ? Enable TRIGGER all”

Question 37 : Fetch the procedures which are created in last 30 days.

Query :

SELECT name,sys.objects.create_date

FROM sys.objects

WHERE type=‘P’

AND DATEDIFF(D,sys.objects.create_date,GETDATE())< 30;

Question 38 : How to recompile the stored procedure.

Query :

EXEC sp_recompile‘Procedure_Name_To_Recompile’;

GO

These are some most important SQL Server Queries which are really very important in SQL Server Queries. If you like this article on SQL Server Queries or if you have any issues with the same kindly comment in comment section.

 

 

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *