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