Hoeist!? Blog of Colin Raaijmakers.

Hoeist!? Blog of Colin Raaijmakers.

Drop All Indexes and Stats in one Script

Development

I am not sure why someone would want to do this, but it was asked on the forums, and I figured I would post the code I created to perform such a nightmarish operation.  As with any post that I make providing code that could be potentially damaging and dangerous, if you use it, you do so at your own risk.  Don't send me emails complaining that you got fired for deleting all the indexes with the scripts on this post.  I am not going to be able to help you fix it, and my recommendation is going to be restore a backup, and start scripting them all off if you still have a job.

DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @indexname SYSNAME
DECLARE @sql NVARCHAR(4000)
DECLARE dropindexes CURSOR FOR

SELECT indexes.name, objects.name, schemas.name
FROM sys.indexes
JOIN sys.objects ON indexes.OBJECT_ID = objects.OBJECT_ID
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
WHERE indexes.index_id > 0
  AND indexes.index_id < 255
  AND objects.is_ms_shipped = 0
  AND NOT EXISTS (SELECT 1 FROM sys.objects WHERE objects.name = indexes.name)
ORDER BY objects.OBJECT_ID, indexes.index_id DESC

SELECT * FROM sys.stats
OPEN dropindexes
FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
WHILE @@fetch_status = 0
BEGIN
  SET @sql = N'DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname)
  PRINT @sql
  EXEC sp_executesql @sql  
  FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername
END
CLOSE dropindexes
DEALLOCATE dropindexes

GO

DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @statsname SYSNAME
DECLARE @sql NVARCHAR(4000)
DECLARE dropstats CURSOR FOR

SELECT stats.name, objects.name, schemas.name
FROM sys.stats
JOIN sys.objects ON stats.OBJECT_ID = objects.OBJECT_ID
JOIN sys.schemas ON objects.schema_id = schemas.schema_id
WHERE stats.stats_id > 0
  AND stats.stats_id < 255
  AND objects.is_ms_shipped = 0
ORDER BY objects.OBJECT_ID, stats.stats_id DESC

OPEN dropstats
FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername
WHILE @@fetch_status = 0
BEGIN
  SET @sql = N'DROP STATISTICS '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@statsname)
  EXEC sp_executesql @sql  
  --PRINT @sql
  FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername
END
CLOSE dropstats
DEALLOCATE dropstats

Read more about, Drop All Indexes and Stats in one Script

Tags: sql, function, index, stats, drop, delete

Published: Wednesday, September 16, 2009

Calculate distance within SQL

Development

I have a Users table in one of my SQL server databases. Each user record has a lat and long pair on it that indicates the location of that user.

My goal is to be able to call a sql stored proc and provide it a lat/long of the location, and have the stored proc query the users table and perform the calc and only return the users who are within a certain distance.

Create a new Function within SQL server:

-- just set @InKilometers to 0 for miles or 1 for km
-- ex:  SELECT dbo.CalcDistanceBetweenLocations (30.123,27.1,28.14,32.23, 0)

CREATE FUNCTION dbo.CalcDistanceBetweenLocations
      (@LatitudeA       FLOAT = NULL,
       @LongitudeA       FLOAT = NULL,
       @LatitudeB       FLOAT = NULL,
       @LongitudeB       FLOAT = NULL,
       @InKilometers      BIT = 0
       )
RETURNS FLOAT
AS
BEGIN
      
      DECLARE @Distance FLOAT

      SET @Distance = (SIN(RADIANS(@LatitudeA)) *
              SIN(RADIANS(@LatitudeB)) +
              COS(RADIANS(@LatitudeA)) *
              COS(RADIANS(@LatitudeB)) *
              COS(RADIANS(@LongitudeA - @LongitudeB)))

      --Get distance in miles
        SET @Distance = (DEGREES(ACOS(@Distance))) * 69.09

      --If specified, convert to kilometers
      IF @InKilometers = 1
            SET @Distance = @Distance * 1.609344

      RETURN @Distance

END

Read more about, Calculate distance within SQL

Tags: sql, function, calculate, distance

Published: Friday, July 03, 2009