SQL Server Queries :

In my previous article i have given some complex sql queries which are useful for the interview purpose.purpose. In this article i would like to explain some most important and most useful 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));

Create view Viewname

 as

Select

column1,column2….column..n;

Create view V_test

as

Select * from

Employee;

Select Column_name from Information_schema.Columns

Where table_name= ‘Tablename’;

Select Name from Sys.objects where Type=’U’;

Select * from Sys.Views;

Create procedure Procedurename

As

Select …….

……..Statements;

Create procedure P_test

as

select name,rollno from

Employee;

end;

exec sp_helptext @objname = ‘Object_Name’ ;

exec sp_helptext @objname = ‘P_test’ ;

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

Select * from Sys.Procedures;

SELECT DISTINCT o.name, o.xtype

FROM syscomments c INNER JOIN sysobjects o ON c.id=o.id WHERE o.xtype=‘P’ ;

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

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.name) AS 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;

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.