Frequently used DBA-Developer Scripts

Frequently Used Queries In SQL Server
Frequently used DMV's in SQL Server

1. Check existence of database in SQL Server

use DB_ID() function
 Example:
 IF DB_ID('Student') IS NOT NULL
   BEGIN
   -- write your logic here
   END

2. Check existence of table in the database in SQL Server

use sys.Tables
 Example:
 IF EXISTS(Select null from sys.Tables where Name = 'Account')
   BEGIN
   -- write your logic here
   END

3. Check existence of Stored Procedure in SQL Server

use sys.procedures
Example:
 IF EXISTS(SELECT null FROM sys.procedures WHERE Name ='sp_Student_Select')
 BEGIN
 -- write your logic here
 END

4. Check existence of Function in SQL Server

 use sys.objects
 Example:
 IF EXISTS (SELECT null FROM sys.objects WHERE Name ='GetSalary')
 BEGIN
 -- write your logic here
 END

5. Create an index on Table in SQL Server

CREATE INDEX IndexName ON TableName(Column1,Column2);

6. Delete duplicate rows from Table in SQL Server

By Using Row_Number
Example:
 WITH AccountTemp (Name,duplicateCount)AS
 (SELECT Name,ROW_NUMBER() OVER(PARTITION by Name ORDER BY Name) AS duplicateCount FROM Account)
 --Now delete records from TempTable
 Delete from AccountTemp WHERE duplicateCount > 1

7. Find Tables without index in SQL Server

SELECT Name as 'TableName' FROM SYS.tables WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasIndex')=0

8. Find Tables without Primary Key in SQL Server

SELECT Name as 'TableName' FROM SYS.Tables 
WHERE OBJECTPROPERTY(object_id,'TableHasPrimaryKey')= 0 AND type= 'U'

9. Find Specific text in Stored Procedure in SQL Server

SELECT OBJECT_NAME(object_id),OBJECT_DEFINITION(object_id) FROM sys.procedures 
WHERE OBJECT_DEFINITION(object_id) LIKE '%SearchText%'

10. Find tables that have specified column name in SQL Server

SELECT SCHEMA_NAME(schema_id)+ '.' +t.name AS 'TableName' FROM 
sys.tables t INNER JOIN sys.columns c ON c.object_id= t.object_id
WHERE c.name like '%ColumnName%'

11. List down all Database in SQL Server

sp_helpdb

12. Get All Stored Procedure present in all Database in SQL Server

SELECT DISTINCT b.name FROM syscomments co INNER JOIN sysobjects b ON co.id=b.id  
WHERE b.xtype='P'

13. Get All Stored Procedure Related To Table in SQL Server

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

14. RESEED Identity of all tables in SQL Server

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

15. Select a List of tables with a number of records

SELECT o.NAME 'Table Name',i.rowcnt 'No of Rows'
FROM sysindexes AS I INNER JOIN sysobjects AS o ON i.id = o.id
WHERE i.indid < 2 AND OBJECTPROPERTY(o.id, 'IsMSShipped') = 0
ORDER BY o.NAME

16. Get Current Language of SQL Server

SELECT @@LANGUAGE AS CurLang;

17. Disable all constraints of a table in SQL Server

ALTER TABLE TableName 
NOCHECK CONSTRAINT ALL

18. Enable all constraints of a table in SQL Server

ALTER TABLE TableName 
CHECK CONSTRAINT ALL

19. Find a list of Stored procedure modified in the last N days in SQL Server

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

20. Find a List of Stored procedures created in the last N days in SQL Server

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

21. Get all columns of a specific data type in SQL Server

SELECT OBJECT_NAME(col.OBJECT_ID) as TableName,col.name as ColumnName
FROM sys.columns AS col JOIN sys.types AS tp ON col.user_type_id=tp.user_type_id
WHERE tp.name ='DataType';

22. Get all Nullable columns of a table in SQL Server

SELECT OBJECT_NAME(col.OBJECT_ID) as TableName, col.name as ColumnName
FROM sys.columns AS col JOIN sys.types AS tp ON col.user_type_id=tp.user_type_id
WHERE col.is_nullable=0 AND OBJECT_NAME(col.OBJECT_ID)='Table_Name';

23. Get the First Date of Current Month in SQL Server

SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-(DAY(GETDATE()))+1,GETDATE()),105) FirstDate;

24. Select first 3 characters in String in SQL Server

SELECT substring('India',1,3);

25. Find Position in String in SQL Server

SELECT CHARINDEX( 'My', 'My India', 1);

26. Get First Name, Year, Month and Date of joining year of an employee in SQL Server

Select SUBSTRING (convert(varchar,joiningdate,103),7,4) , SUBSTRING
(convert(varchar,joiningdate,100),1,3), SUBSTRING(convert(varchar,joiningdate,100),5,2) 
from Emp;

27. Find Second highest salary from Employee table in SQL Server

SELECT MAX(Sal) FROM Emp WHERE Sal NOT IN (select MAX(Sal) from Emp;

28. Find Maximum Salary from each department in SQL Server

SELECT DepartmentID,MAX(Sal) FROM Emp GROUP BY DepartmentID;

29. Check Whether the given date is in a particular format or not in SQL Server

SELECT ISDATE('01/24/17') AS 'MM/DD/YY';

30. Find Odd rows from a table

SELECT E.BrandId, E.Brand FROM (
SELECT *, Row_Number() OVER(ORDER BY BrandId) AS RowNumber FROM Brand) E
WHERE E.RowNumber % 2 = 1

31. Find even rows from a table

SELECT E.BrandId, E.Brand
FROM (SELECT *, Row_Number() OVER(ORDER BY BrandId) AS RowNumber FROM Brand) 
E WHERE E.RowNumber % 2 = 1

 32. Find Most used tables In SQL Server

SELECT db_name(ius.database_id) AS DatabaseName, tbl.NAME AS TableName,
SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) AS NbrTimesAccessed
FROM sys.dm_db_index_usage_stats ius INNER JOIN sys.tables tbl ON tbl.OBJECT_ID = ius.object_id
WHERE database_id = DB_ID('DV_Migration') GROUP BY database_id,tbl.name
ORDER BY SUM(ius.user_seeks + ius.user_scans + ius.user_lookups) DESC

 33. Find most-used indexes In SQL Server

SELECT db_name(ius.database_id) AS DatabaseName,
 tbl.NAME AS TableName,i.NAME AS IndexName,i.type_desc AS IndexType,
 ius.user_seeks + ius.user_scans + ius.user_lookups AS NbrTimesAccessed
 FROM sys.dm_db_index_usage_stats ius
 INNER JOIN sys.indexes i ON i.OBJECT_ID = ius.OBJECT_ID AND i.index_id = ius.index_id
 INNER JOIN sys.tables tbl ON tbl.OBJECT_ID = i.object_id
 WHERE database_id = DB_ID('DV_Migration')
 ORDER BY ius.user_seeks + ius.user_scans + ius.user_lookups DESC

34. Find Most frequently run queries in SQL Server

SELECT [Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt ORDER BY qs.execution_count desc;

35. Find the size of the log for each database

DBCC SQLPERF (LOGSPACE);

36. Find what request is running on the server

SELECT SUBSTRING(dest.text, ( deqs.statement_start_offset / 2 ) + 1,
( CASE deqs.statement_end_offset WHEN -1 THEN DATALENGTH(dest.text)
ELSE deqs.statement_end_offset- deqs.statement_start_offset
END ) / 2 + 1) AS querystatement ,deqp.query_plan ,deqs.execution_count ,deqs.total_worker_time ,deqs.total_logical_reads ,deqs.total_elapsed_time FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan(deqs.plan_handle) AS deqp;

37. Find disk space of individual objects, tables, and indexes in SQL server

EXEC sys.sp_spaceused @objname = N'Sales.SalesOrderHeader';

38. Finding duplicate records in Table in SQL Server

SELECT Name, Count(Name) FROM TableName GROUP BY Name
HAVING Count(Name)>1 

39. Query for Displaying Primary Keys  in SQL Server

SELECT * FROM Sys.Objects WHERE Type='PK'