Ms SQL Useful Stored Procedures

DBCC CHECKDB ('DB_Name')
DBCC CHECKALLOC ('DB_Name')
DBCC updateusage ('DB_Name')
DBCC CHECKIDENT ('dbo.tablename',reseed,0) 
- '0' by default,Seed Value Last Incremented value can be added

EXEC SP_help

EXEC SP_helptext

EXEC sp_renamedb 'oldname' , 'newname';

xp_fixeddrives  - To get HD available in the Server / Disk Space

To Get List of Jobs running in SQL Agent Job

How to find the SQL Agent Jobs  running in your DB Instance and its Steps, query used for the results

 SELECT J.NAME AS 'JOB NAME',

S.STEP_ID AS 'STEP',

S.STEP_NAME AS 'STEP NAME',

S.COMMAND AS 'QUERY',

DATABASE_NAME AS 'DATABASE'

FROM MSDB.DBO.SYSJOBS J

INNER JOIN MSDB.DBO.SYSJOBSTEPS S ON S.JOB_ID = J.JOB_ID

WHERE J.ENABLED = 1

ORDER BY J.NAME,S.STEP_ID

 

How to Enable Automatic Statistic Update on Database

After putting good indexes and optimizing the query, I could not achieve an optimized performance and I still received an unoptimized response from the SQL Server. On examination, I figured out that the culprit was statistics. The database that I was trying to optimize had auto update of the statistics was disabled.

Once I enabled the auto update of statistics, the database started to respond as expected. If you ever face situation like this, please do the following:

1) Enable Auto Creation of Statistics

2) Enable Auto Update of Statistics

3) Update Statistics for whole database

Display All IP address used in a LAN

A single line of a command and its arguments that can help display all computer IP addresses (those that are being used) on a LAN,

arp -a

ipconfig /all

net view /all

nmap -sn 192.168.0.0/24 (replacing the sub-net with the appropriate one for your LAN)

MS SQL Server Main Replication Models

Replication Publisher and Subscriber :

To understand how SQL replication works, we’ll have to define two replication terms – Publisher and Subscriber. In a SQL replication the Publisher is the part that offers the data for distribution. The Subscriber is the part that consumes (gets updates) the data from the Publisher.

MS SQL : Use of GUID function

If you want to generate a new Guid (Unique Identifier) in SQL server the you can simply use the NEWID() function.

Example

SELECT NEWID( )
GO
-- This will return a new random uniqueidentifier e.g.
E75B92A3-3299-4407-A913-C5CA196B3CAB

To select this Guid in in a variable

--To Assign uniqueidentifier in a variable
DECLARE @EmployeeID uniqueidentifier
SET @EmployeeID = NEWID( )

You can directly use this with INSERT statement to insert new row in table.

-- Inserting data in Employees table.
INSERT INTO Employees
(EmployeeID, Name, Phone)
VALUES
(NEWID( ), 'Shyam Sundar', '123-4567')

MS SQL : Function to Convert INR Value

Function (numberVar Amount)

numbervar RmVal:=0;

numbervar Amt:=0;

numbervar pAmt:=0;

stringvar InWords :="";

stringvar ckAmt:="";

Amt := ABS(Amount);

ckAmt := CSTR(ABS(Amount));