Query to get CPU and memory Information

Run below query to get the CPU and memory information:


EXEC xp_instance_regread 'HKEY_LOCAL_MACHINE', 'HARDWARE\DESCRIPTION\System\CentralProcessor\0', 'ProcessorNameString';


-- Query to get CPU and Memory Info
SELECT CPU_Count AS [No. of Logical CPU], CPU_Count/Hyperthread_Ratio AS [No of Physical CPU], Physical_Memory_In_Bytes/1048576 AS [Total Physical Memory (MB)] 
FROM sys.dm_os_sys_info OPTION (RECOMPILE)

SQL script to create Folder on local disk

We can create Folder in local Hard Disk directly from SQL. Check below script for Create Folder. Below query first check for the existence of folder in HDD if not found than it creates new.


DECLARE @ChkFolder AS nvarchar(4000)
DECLARE @Folder_Exists AS INT
set @ChkFolder = 'C:\Temp\'
DECLARE @File_Result TABLE
(File_Exists INT,
File_Is_A_Folder INT,
Parent_Folder_Exists INT
)
INSERT INTO @File_Result
(File_Exists, File_Is_A_Folder, Parent_Folder_Exists)
EXEC [master].dbo.xp_FileExist @ChkFolder
SELECT @Folder_Exists = File_Is_A_Folder
FROM @File_Result
--script to create Folder
if @Folder_Exists = 0
BEGIN
PRINT 'Folder is not exists, creating new one '
EXECUTE [master].dbo.xp_Create_SubDir @ChkFolder
PRINT @ChkFolder +  'created on ' + @@servername
END
ELSE
PRINT 'Folder already exists'
GO

How to get rid of the time part from the date returned by GETDATE function?

There are various ways you can get Date only from DateTime.


You can use DATEPARTfunction to get Date only 

SELECT LTRIM(RTRIM(STR(DATEPART(YY, GETDATE())))) + '-' + LTRIM(RTRIM(STR(DATEPART(M, GETDATE())))) + '-' + LTRIM(RTRIM(STR(DATEPART(D, GETDATE()))))

You can use Convert Function. It is better than above.
You have to use the CONVERT function to strip the time off the date. Any of the following commands will do this:

SELECT CONVERT(char,GETDATE(),101)
SELECT CONVERT(char,GETDATE(),102)
SELECT CONVERT(char,GETDATE(),103)
SELECT CONVERT(char,GETDATE(),1)



See SQL Server Books Online for more information on CONVERT function.

You can also do like this

SELECT CAST(FLOOR(CAST(GETDATE() AS FLOAT)) AS DATETIME) AS Date_Only

If you do not want thetimepart at all better declare the column as smalldatetime

Check Hard Disk Space

CREATE PROCEDURE sp_totalHDDspace
AS
SET NOCOUNT ON

DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint ; SET @MB = 1048576

CREATE TABLE #drives (drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL)

INSERT #drives(drive,FreeSpace)
EXEC master.dbo.xp_fixeddrives

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD
FOR SELECT drive from #drives
ORDER by drive

OPEN dcur

FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0
BEGIN

EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @odrive

UPDATE #drives
SET TotalSize=@TotalSize/@MB
WHERE drive=@drive

FETCH NEXT FROM dcur INTO @drive

END

CLOSE dcur
DEALLOCATE dcur

EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

SELECT drive,
FreeSpace as 'Free(MB)',
TotalSize as 'Total(MB)',
CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as 'Free(%)'
FROM #drives
ORDER BY drive

DROP TABLE #drives

RETURN
go

Insert into Table with Default Values only

Sometimes we may need to insert a row into Table with Default Values and don't want to provide column list. Use below Query:


INSERT INTO CustomerMaster Default Values


If some columns do not have default values assigned than NULL will be inserted. And if there is a column with no Default value and has NOT NULL constraint, then Insert Statement will fail.