UmaMahesh.Net

The Below script will brings the Missing Indexes in the databases -- =================================================== -- Author: V.U.M.Sastry Sagi -- Create date: 01/30/2011 -- Description: Fetches the Missing Indexes -- =================================================== CREATE PROCEDURE [DBO].[FINDMISSINGINDEXES] AS BEGIN SELECT MID.STATEMENT, MIGS.AVG_TOTAL_USER_COST * ( MIGS.AVG_USER_IMPACT / 100.0 ) * ( MIGS.USER_SEEKS + MIGS.USER_SCANS ) AS IMPROVEMENT_MEASURE, OBJECT_NAME(MID.OBJECT_ID), 'CREATE INDEX [MISSING_INDEX_'

The Below code will fetches each Table Name,# of Rows in that table, Reserved Size, Index Size,UnUsed Space in the given Database. -- =================================================== -- Author: V.U.M.Sastry Sagi -- Create date: 01/12/2011 -- Description: Fetches the tables and the size useage of each table -- =================================================== CREATE PROCEDURE [DBO].[SPACEUSED] @SOURCEDB VARCHAR(128) AS SET NOCOUNT ON

Below Code will execute FTP Commands from SQL Server. First thing you need to take care is create a text file with the name “ftp” and type all your ftp commands and then create the below procedure in SQL Server -- ============================================= -- Author: V.U.M.Sastry Sagi -- Create date:12/28/2010 -- Description: Executes a FTP Command

The Below Procedure will drop the Database by force though it is accessing by the End Users. -- ============================================= -- Author: V.U.M.Sastry Sagi -- Create date: 12/05/2010 -- Description: Drops a Database by Force -- ============================================= CREATE PROCEDURE [dbo].[dropDBByForce](@DBName VARCHAR(100)) AS BEGIN DECLARE @sql NVARCHAR(MAX) SET @sql='ALTER DATABASE ['+@dbname+'] SET SINGLE_USER --or RESTRICTED_USER WITH ROLLBACK

The below code will calculate the distance (Displacement) between two locations based on longitude and latitude. -- ============================================= -- Author: V.U.M.Sastry Sagi -- Create date: 11/30/2010 -- Description: Gets the distance between two locations -- ============================================= CREATE FUNCTION [dbo].[getDistanceBetweenLatLon] ( @lat1 DECIMAL(5, 2), @long1 DECIMAL(5, 2), @lat2 DECIMAL(5, 2), @long2 DECIMAL(5, 2) ) RETURNS NUMERIC(10,