Get first date of next month SQL

Get first date of next month SQL


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

Get last date of current month SQL

Get last date of current month SQL

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

Get last date of previous month SQL

Get last date of previous month SQL


SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-

(DAY(GETDATE())),GETDATE()),105) Last_Date_Previous_Month;  

Get First Date of Current Month SQL

Get First Date of Current Month SQL

SELECT CONVERT(VARCHAR(25),DATEADD(DAY,-

(DAY(GETDATE()))+1,GETDATE()),105) First_Date_Current_Month; 

Get All table that don’t have identity column

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;

Get All table that don’t have foreign key

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; 

Get All table that don’t have primary key

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; 

Get all Nullable columns of a table

Get all Nullable columns of a table


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

Get all columns of a specific data type

Get all columns of a specific data type:


SELECT OBJECT_NAME(c.OBJECT_ID) as Table_Name, c.name as Column_Name  
  
FROM sys.columns AS c  
  
JOIN sys.types AS t ON c.user_type_id=t.user_type_id  
  
WHERE t.name = 'Data_Type'

Recompile all stored procedure on a table

Recompile all stored procedure on a table


EXEC sp_recompile N'Table_Name';  
  
GO  

Recompile a stored procedure

Recompile a stored procedure


EXEC sp_recompile'Procedure_Name';  
  
GO  

List of Stored procedure created in last N days

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  

List of Stored procedure modified in last N days

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

Disable and Enable All Trigger for database

Disable and Enable All Trigger for database


Use Database_Name  
  
Exec sp_msforeachtable "ALTER TABLE ? DISABLE TRIGGER all"

Use Demo  
  
Exec sp_msforeachtable "ALTER TABLE ? ENABLE TRIGGER all"

Disable and Enable All Triggers of a table

Disable and Enable All Triggers of a table

ALTER TABLE Table_Name DISABLE TRIGGER ALL 

ALTER TABLE Table_Name ENABLE TRIGGER ALL

Enable a Particular Trigger SQL

Enable a Particular Trigger SQL

ALTER TABLE Table_Name ENABLE TRIGGER Trigger_Name

Disable a Particular Trigger SQL

Disable a Particular Trigger SQL

ALTER TABLE Table_Name DISABLE TRIGGER Trigger_Name

Retrieve Free Space of Hard Disk

Retrieve Free Space of Hard Disk


EXEC master..xp_fixeddrives 

Get Session Id of current user process

Get Session Id of current user process


SELECT @@SPID AS 'Session_Id'  

Get name of register key under which SQL Server is running

Get name of register key under which SQL Server is running


SELECT @@SERVICENAME AS 'Service_Name'