Scripts (MSSQL)

Below are some Microsoft SQL Server (MSSQL) scripts I find myself coming back to from time to time.  Feel free to use anything you want and check back occasionally as this page will be under a perpetual state of construction.

Table of Contents

Cursor Template
Custom DB Role: db_execute
Databases Failing Backup SLAs
Date/Time Partition Function
Maintenance Plan Execution History
Script out a Database Role
Server\Instance Information
Superfluous Indexes

Cursor Template

I created this when I first started as a DBA. Since I rarely use cursors I reference this if I ever find a need for one:

-- Declare the cursor
DECLARE myCursor CURSOR
FOR
    SELECT Col1, Col2, etc...
    FROM Table1
    WHERE Condition = 'something'
    ORDER BY IsNotNecessary

-- Declare your parameters that will hold that current row's values
--  when traversing through the cursor
DECLARE @myCol1 INT, @myCol2 VARCHAR(50), @myetc...

-- Inject the data into the cursor
OPEN myCursor
FETCH NEXT FROM myCursor
INTO @myCol1, @myCol2, @myetc...

-- Enter the While Loop.  This loop will end when you reach the 
--  end of the data you injected into the cursor.
WHILE @@FETCH_STATUS = 0
BEGIN

    -- Here you can manipulate any part of your parameters
    -- The following is an example
    INSERT INTO #somePreviousDeclaredTempTable
        (Columns..., @myCol1, @myCol2, @myetc...)
    VALUES ((SELECT COLUMNS FROM OtherTable WHERE PKEY = @myCol1), @myCol1, @myCol2, @myetc...)

    -- Traverse the Data in the cursor
    FETCH NEXT FROM myCursor
    INTO @myCol1, @myCol2, @myetc...
END

-- Close and deallocate the cursor because you've finished traversing all it's data
CLOSE myCursor
DEALLOCATE myCursor

Databases Failing Backup SLAs

Here’s a handy script to determine which database(s) within your organization are failing to meet your established backup SLAs.  This will fit easily into a SQL Server job that emails you the report (if any output is present) on a daily basis to allow for streamlined administration.

Custom DB Role: DB_EXECUTE

This is a custom database role I often add to the model database during my Post-Installation Configuration process. This role grants execute rights against any stored procedure in the database. This is helpful for those users that require read/write/execute permissions.

USE [model]
GO
CREATE ROLE [db_execute]
GRANT EXECUTE TO [db_execute]

Date/Time Partition Function

This scalar-valued UDF converts any date/time value passed to it into a 15 minute bucket of time.  I’ve put comments into it going over the calculation so you can modify it for buckets of your choosing.  This can be helpful when aggregating data by chunks of time.

CREATE FUNCTION dbo.TimePartition
(
    @myDate SMALLDATETIME
)
RETURNS SMALLDATETIME
AS
BEGIN
     RETURN (
        SELECT
        CONVERT(SMALLDATETIME,
            CONVERT(NUMERIC(10, 5),
                -- Convert to an INT for easier math
                CONVERT(INT,
                    CONVERT(NUMERIC(10, 5),
                        -- Date is inconsiquential because we want increments of time.  
                        --  Strip off Date by Subtracting Day 0 from the Date
                        CONVERT(NUMERIC(10, 5), @myDate)
                            - DATEDIFF(d, 0, @myDate)
                    )
                    -- Multiply by Number of Hours in a day, then by Number of Partitions you want per Hour
                    --  and the 0/1 will determine if bucket falls "down" or "up" to the next partition
                    * 24 /*Hours in a Day*/
                    * 4 /*# of Partitions/Hour*/
                    + 0 /*0 if go down 1 if go up*/
                )
            -- divide by this same amount to strip any excess information off of the partitions
            ) / 24 / 4
        -- Add back the Date taken off in the second step and Convert back to Datetime
        + DATEDIFF(d, 0, @myDate))
    )
END

Maintenance Plan Execution History

If you utilize Maintenance Plans in SQL Server, you may often find that the job history is absent of sufficient detail.  This routine will show all execution history available for the Maintenance Plan you specify.

-- Enter the Maintenance Plan Name in the Variable below.  
-- If thre is a . in the name, enter everything before that,
--    e.g. if Backup Databases.Daily, enter Backup Databases
DECLARE @PlanName NVARCHAR(255)

SET @PlanName = 'Maintenance Plan Name'

SELECT    ld.line1 AS Line1,
        ld.line2 AS Line2,
        ld.line3 AS Line3,
        ld.line4 AS Line4,
        ld.line5 AS Line5,
        ld.server_name AS ServerName,
        ld.start_time AS StartTime,
        ld.end_time AS EndTime,
        ld.succeeded AS Succeeded,
        ld.error_number AS ErrorNumber,
        ld.error_message AS ErrorMessage,
        ld.command AS Command
FROM    msdb.dbo.sysmaintplan_plans s
        INNER JOIN msdb.dbo.sysmaintplan_subplans sp
            ON sp.plan_id = s.id
        INNER JOIN msdb.dbo.sysmaintplan_log spl
            ON spl.subplan_id = sp.subplan_id
        INNER JOIN msdb.dbo.sysmaintplan_logdetail ld
            ON ld.task_detail_id = spl.task_detail_id
WHERE    s.name = @PlanName
ORDER BY StartTime DESC

Script out a Database Role

One of the shortcommings of SQL Server Management Studio is it’s inability to script out a database role. I went to some lengths to come up with a custom script that will do this for you rather quickly. Check out the full post here.

Server\Instance Information

This will return expanded server information and instance information of your choosing.  If you wish to expand the information returned about the instance look at the SERVERPROPERTY function for more options.

-- Expanded Server Information
EXEC xp_msver
-- Instance Information (http://msdn.microsoft.com/en-us/library/ms174396.aspx)
SELECT  SERVERPROPERTY('ProductVersion') AS [Version],
    SERVERPROPERTY('ProductLevel') AS [Service Pack],
    SERVERPROPERTY('Edition') AS [Edition],
    SERVERPROPERTY('ProcessID') AS [Task Manager PID]

Superfluous Indexes

This will display all non-clustered indexes in a database that have more than 10000 records, but have been accessed less than 100 times since the most recent instance restart, time of index creation, or detach/attach operation for said database.  Feel free to adjust the number of records and reads as required to hone in on superfluous (aka underutilized) indexes in your environment.

SELECT  OBJECT_NAME(s.OBJECT_ID) AS [TableName],
        i.name AS [IndexName],
        i.index_id AS [IndexID],
        user_seeks + user_scans + user_lookups AS [TotalReads],
        user_updates AS [Updates],
        p.ROWS AS [Records],
        (ps.used_page_count * 8 )/ 1024 AS [IndexSize(MB)]
FROM sys.dm_db_index_usage_stats s
        JOIN sys.indexes i
            ON i.index_id = s.index_id
            AND s.OBJECT_ID = i.OBJECT_ID
        JOIN sys.partitions p
            ON p.index_id = s.index_id
            AND s.OBJECT_ID = p.OBJECT_ID
        JOIN sys.dm_db_partition_stats ps
            ON ps.index_id = i.index_id
            AND ps.OBJECT_ID = i.OBJECT_ID
WHERE OBJECTPROPERTY(s.OBJECT_ID,'IsUserTable') = 1
    AND s.database_id = DB_ID()
    AND i.type_desc = 'NonClustered'
    -- Adjust the following values as needed
    AND p.ROWS > 10000
    AND user_seeks + user_scans + user_lookups < 100
ORDER BY TotalReads, Records DESC

Leave a comment