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'  

Return Server Name of SQL Server

Return Server Name of SQL Server

SELECT @@SERVERNAME AS 'Server_Name'

Get precision level used by decimal and numeric as current set in Server

Get precision level used by decimal and numeric as current set in Server

SELECT @@MAX_PRECISION AS 'MAX_PRECISION' 

Get Current Language Id

Get Current Language Id


SELECT @@LANGID AS 'Language ID' 

Disable all constraints of all tables

Disable all constraints of all tables


EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'  

Disable all constraints of a table

Disable all constraints of a table

ALTER TABLE Table_Name NOCHECK CONSTRAINT ALL

Get Current Language of SQL Server

Get Current Language of SQL Server



SELECT @@LANGUAGE AS Current_Language;  

Get the version name of SQL Server

Get the version name of SQL Server


SELECT @@VERSION AS Version_Name  

List of tables with number of records

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;

RESEED Identity of all tables

RESEED Identity of all tables


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

List of Primary Key and Foreign Key for a particular table

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  

List of Primary Key and Foreign Key for Whole Database

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  

Get all table that don’t have identity column

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

Find Byte Size Of All tables in database

Find Byte Size Of All tables in database:



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

Retrieve All dependencies of Stored Procedure:

Retrieve All dependencies of Stored Procedure:


;WITH stored_procedures AS (  
  
SELECT  
  
oo.name AS table_name,  
  
ROW_NUMBER() OVER(partition by o.name,oo.name ORDER BY o.name,oo.name) AS row  
  
FROM sysdepends d  
  
INNER JOIN sysobjects o ON o.id=d.id  
  
INNER JOIN sysobjects oo ON oo.id=d.depid  
  
WHERE o.xtype = 'P' AND o.name LIKE '%SP_NAme%' )  
  
SELECT Table_name FROM stored_procedures  
  
WHERE row = 1