Microsoft SQL Server – 50+ useful Sql query

Microsoft SQL Server – 50+ useful Sql query

1- Sql query to get all Views relate to table

SELECT DISTINCT so.name, so.xtype
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE ‘%Table_Name%’ AND so.xtype=’V’

2- Sql query to Get Session Id of current user process

SELECT @@SPID AS ‘Session_Id’



3- Sql query to get all table that don’t have identity column

SELECT name AS Table_Name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,’TableHasIdentity’) = 0
ORDER BY Table_Name;



4- Sql query to Rebuild All Index of Database

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

5-Sql query to Get Current Value of TEXTSIZE option

SELECT @@TEXTSIZE AS ‘Text_Size’

6- Sql query to Get all table that don’t have identity column

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 sic
on
(c.COLUMN_NAME=sic.NAME))
AND
TABLE_TYPE =’BASE TABLE’



7- Sql query to swap the values of two columns

UPDATE Table_Name SET Column1=Column2, Column2=Column1



8- Sql query to disable a Particular Trigger
ALTER TABLE Table_Name DISABLE TRIGGER Trigger_Name

Example:

ALTER TABLE Employee DISABLE TRIGGER TR_Update_qualificationDate

9- Sql query to Find Byte Size of All tables in database

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



10- Sql query to get last date of current month

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



11- Retrieve Free Space of Hard Disk

EXEC master..xp_fixeddrives

12- Sql query to retrieve all dependencies of Stored Procedure

;WITH stored_procedures AS (
SELECT
so1.name AS table_name,
ROW_NUMBER() OVER(partition by so.name, so1.name ORDER BY so.name, so1.name) AS row
FROM sysdepends sd
INNER JOIN sysobjects so ON so.id=sd.id
INNER JOIN sysobjects so1 ON so1.id=sd.depid
WHERE so.xtype = ‘P’ AND so.name LIKE ‘%SP_Name%’ )
SELECT Table_name FROM stored_procedures
WHERE row = 1



13- Sql query to get last date of previous month

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

14- Sql query to List of Primary Key and Foreign Key for Whole Database

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



15- Sql query to drop all tables

EXEC sys.sp_MSforeachtable @command1 = ‘Drop Table?’

16- Sql query to enable a Particular Trigger

ALTER TABLE Table_Name ENABLE TRIGGER Trigger_Name
Example:
ALTER TABLE Employee ENABLE TRIGGER TR_Update_qualificationDate

17- Sql query to get all columns contain any constraints

SELECT TABLE_NAME, COLUMN_NAME,CONSTRAINT_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE



18- Sql query to return Server Name of SQL Server

SELECT @@SERVERNAME AS ‘Server_Name’



19- SQL Server: Sql query to get information of tables columns

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE INFORMATION_SCHEMA.COLUMNS.TABLE_NAME=’Table_Name’



20- Sql query to get Current Language Id

SELECT @@LANGID AS ‘Language ID’

21- Sql query to disable all constraints of all tables

EXEC sp_MSForEachTable ‘ALTER TABLE ? NOCHECK CONSTRAINT ALL’



22- Sql query to retrieve List of All Database

Answer: EXEC sp_helpdb

23- Sql query to List of Stored procedure created in last N days

SELECT name, sys.objects.create_date
FROM sys.objects
WHERE type=’P’
AND DATEDIFF(D,sys.objects.create_date,GETDATE())< N



24- Sql query to display Text of Stored Procedure, Trigger, and View

exec sp_helptext @objname = ‘Object_Name’
In this query Object_name can be stored procedure, trigger or view name.
Example:
exec sp_helptext @objname = ‘sp_GetLatestOrders’

25- Sql query to recompile a stored procedure

EXEC sp_recompile’Procedure_Name’;

26- Sql query to get all stored procedure related to database

SELECT DISTINCT so.name, so.xtype
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE so.xtype=’P’



27- Sql query to recompile all stored procedure on a table

EXEC sp_recompile N’Table_Name’;

28- Sql query to get All Stored Procedure Relate to Table

SELECT DISTINCT so.name, so.xtype
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE ‘%Table_Name%’ AND so.xtype=’P’

29- Sql query to get all Nullable columns of a table

SELECT OBJECT_NAME(sc.OBJECT_ID) as Table_Name, sc.name as Column_Name
FROM sys.columns AS sc
JOIN sys.types AS st ON sc.user_type_id=st.user_type_id
WHERE sc.is_nullable=0 AND OBJECT_NAME(sc.OBJECT_ID)=’Table_Name’

30 – Write SQL Query to display current date.

SQL has built in function called GetDate () which returns current timestamp.

31 – Write an SQL Query to find names of students start with ‘A’.

SELECT * FROM Employees WHERE Students like ‘A%’

32- Sql query to list names of all the tables in a sql server database
SELECT * FROM INFORMATION_SCHEMA.TABLES

33- How to delete all duplicate rows from table but keeping one record?

Because of some service error we got lots of duplicate records in our database table. We need to delete all the duplicates but we want to keep one record in our table.

Below was the query which we used to do the same.

WITH deleteDuplicates AS (

SELECT[WebURL], ID,

row_number() OVER(PARTITION BY [WebURL] ORDER BY Id) AS [rownum]

FROM table_name

)

DELETE deleteDuplicates WHERE [rownum] > 1

 

34-Sql query to RESEED Identity of all tables

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

35- Sql query to 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

36- Sql query to get all columns of table that using in views

SELECT * FROM INFORMATION_SCHEMA.VIEW_COLUMN_USAGE

37- Sql query to get name of register key under which SQL Server is running

SELECT @@SERVICENAME AS ‘Service_Name’

38- Sql query to disable all constraints of a table

ALTER TABLE Table_Name NOCHECK CONSTRAINT ALL

39- Sql query to get Current Language of SQL Server

SELECT @@LANGUAGE AS Current_Language;

40- Sql query to get the version name of SQL Server

SELECT @@VERSION AS Version_Name

41- Sql query to 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;

42- Sql query to List of Stored procedure modified in last N days

SELECT name, modify_date
FROM sys.objects
WHERE type=’P’
AND DATEDIFF(D,modify_date,GETDATE())< N

43- Sql query to Enable All Trigger for database

Use Database_Name
Exec sp_msforeachtable “ALTER TABLE ? ENABLE TRIGGER all”

44- Sql query to Disable All Trigger for database

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

Use Database_Name

Exec sp_msforeachtable “ALTER TABLE ? DISABLE
TRIGGER all”

45- Sql query to Enable All Trigger of a table

ALTER TABLE Table_Name ENABLE TRIGGER ALL

Example:

ALTER TABLE MembershipFee ENABLE TRIGGER ALL

46- Sql query to Disable All Trigger of a table

SQL Server: Sql query to 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.

ALTER TABLE Table_Name DISABLE TRIGGER ALL

Example:

ALTER TABLE MembershipFee DISABLE TRIGGER ALL

47- Sql query to get all tables that contain a view

SELECT * FROM INFORMATION_SCHEMA.VIEW_TABLE_USAGE

48- Sql query to get all table that don’’t have foreign key

SELECT name AS Table_Name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,’TableHasForeignKey’) = 0
ORDER BY Table_Name;

49- Sql query to get all table that don’t have primary key

SELECT name AS Table_Name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,’TableHasPrimaryKey’) = 0
ORDER BY Table_Name;

50- Sql query to select unique records by SQL

SELECT DISTINCT column 1, column 2, …
FROM table_name;