Uneven Technology: July 2013

-- This script finds the size of each table within the DB
-- Can't remember where I downloaded it from originally

CREATE PROCEDURE GetAllTableSizes
AS

DBCC UPDATEUSAGE (0)
DECLARE @TableName VARCHAR(100) --For storing values in the cursor

--Cursor to get the name of all user tables from the sysobjects listing

DECLARE tableCursor CURSOR FOR

SELECT [name] FROM dbo.sysobjects WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1 FOR READ ONLY

--A procedure level temp table to store the results

CREATE TABLE #TempTable
(
      tableName varchar(100),
      numberofRows varchar(100),
      reservedSize varchar(50),
      dataSize varchar(50),
      indexSize varchar(50),
      unusedSize varchar(50)
)

OPEN tableCursor

FETCH NEXT FROM tableCursor INTO @TableName

WHILE (@@Fetch_Status >= 0)

BEGIN
      IF (SUBSTRING(@TableName, 1, 3) = 'PX_')
      BEGIN
          SELECT @TableName = 'dba.' + @TableName
      END

      --Dump the results of the sp_spaceused query to the temp table
      INSERT #TempTable EXEC sp_spaceused @TableName

FETCH NEXT FROM tableCursor INTO @TableName

END

CLOSE tableCursor

DEALLOCATE tableCursor

--Select all records so we can use the reults

SELECT
    tableName TableName,
    numberofRows NumberofRows,
    CAST(CASE WHEN CHARINDEX(' KB', reservedSize) > 0 THEN SUBSTRING(reservedSize, 1, CHARINDEX(' KB', reservedSize)) ELSE reservedSize END AS INT) ReservedSize,
    CAST(CASE WHEN CHARINDEX(' KB', dataSize) > 0 THEN SUBSTRING(dataSize, 1, CHARINDEX(' KB', dataSize)) ELSE dataSize END AS INT) DataSize,
    CAST(CASE WHEN CHARINDEX(' KB', indexSize) > 0 THEN SUBSTRING(indexSize, 1, CHARINDEX(' KB', indexSize)) ELSE indexSize END AS INT) IndexSize,
    CAST(CASE WHEN CHARINDEX(' KB', unusedSize) > 0 THEN SUBSTRING(unusedSize, 1, CHARINDEX(' KB', unusedSize)) ELSE unusedSize END AS INT) UnusedSize
FROM #TempTable
ORDER BY 4 desc

DROP TABLE #TempTable

/* This improved version of sp_SQLNotify. Added features:

1. Send HTML text

2. Send file attachments

NOTE: Need to download and install Microsoft Access Database Engine 2010 Redistributable

NOTE: Need to install SpliMe SQL function

NOTE: sp_SQLNotify is originally from

http://www.howtogeek.com/howto/database/sending-automated-job-email-notifications-in-sql-server-with-smtp/

Example:

EXEC sp_SQLNotify

'from@email.com',

'to@email.com',

'Your daily missed sales data report',

'<!DOCTYPE html><html><body><h1>My First Heading</h1><p>My first paragraph.</p></body></html>',

'C:\Temp\Test.txt;'

SET ANSI_NULLS OFF

SET QUOTED_IDENTIFIER OFF

ALTER PROCEDURE [dbo].[sp_SQLNotify]

@From varchar(500),

@To varchar(500),

@Subject varchar(1000)= "",

@Body varchar(4000) = "",

@Attachments varchar(2000) = "",

@Cc varchar(500) = NULL,

@BCc varchar(500) = NULL

/*********************************************************************

This stored procedure takes the above parameters and sends an e-mail.

All of the mail configurations are hard-coded in the stored procedure.

Comments are added to the stored procedure where necessary.

Reference to the CDOSYS objects are at the following MSDN Web site:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

***********************************************************************/

Declare @iMsg int

Declare @hr int

Declare @source varchar(500)

Declare @description varchar(500)

Declare @output varchar(4000)

Declare @property int

--************* Create the CDO.Message Object ************************

EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--***************Configuring the Message Object ******************

-- This is to configure a remote SMTP server.

-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp

EXEC @hr = sp_OASetProperty@iMsg,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'

-- This is to configure the Server Name or IP address.

-- Replace MailServerName by the name or IP of your SMTP Server.

EXEC @hr = sp_OASetProperty@iMsg,'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value','10.1.1.56'

-- Save the configurations to the message object.

EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.

EXEC @hr = sp_OASetProperty @iMsg, 'To', @To

EXEC @hr = sp_OASetProperty @iMsg, 'From', @From

EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

EXEC @hr = sp_OASetProperty @iMsg, 'Cc', @Cc

EXEC @hr = sp_OASetProperty @iMsg, 'Bcc', @BCc

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.

-- EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body

EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body

-- Add all attachements to the email

IF @Attachments <> ''

BEGIN

DECLARE @FilePath VARCHAR(200)

DECLARE AttachmentCollection CURSOR FOR SELECT LTRIM(RTRIM(Line))FROMSplitMe(@Attachments, ';')

OPEN AttachmentCollection

FETCH NEXT FROM AttachmentCollection INTO @FilePath

WHILE @@FETCH_STATUS = 0

BEGIN

EXEC @hr = sp_OAMethod @iMsg, 'AddAttachment', @property OUT, @FilePath

FETCH NEXT FROM AttachmentCollection INTO @FilePath

END

CLOSE AttachmentCollection

DEALLOCATE AttachmentCollection

END

-- Send the email

EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.

IF @hr <>0

select @hr

BEGIN

EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT

IF @hr = 0

BEGIN

SELECT @output = ' Source: ' + @source

PRINT @output

SELECT @output = ' Description: ' + @description

PRINT @output

END

ELSE

BEGIN

PRINT ' sp_OAGetErrorInfo failed.'

RETURN

END

-- Do some error handling after each step if you need to.

-- Clean up the objects created.

EXEC @hr = sp_OADestroy @iMsg

PRINT 'Mail Sent!'

Uneven Technology

Friday, July 26, 2013

MSSQL - Get Table Sizes from a DB

Wednesday, July 24, 2013

MSSQL - Improved sp_SQLNotify