a0002: TSQL

Showing posts with label TSQL. Show all posts

Monday, December 29, 2008

Scheduling Jobs in SQL Server Express - SQLTeam.com

Scheduling Jobs in SQL Server Express - SQLTeam.com
As we all know SQL Server 2005 Express is a very powerful free edition of SQL Server 2005. However it does not contain SQL Server Agent service. Because of this scheduling jobs is not possible. So if we want to do this we have to install a free or commercial 3rd party product. This usually isn't allowed due to the security policies of many hosting companies and thus presents a problem. Maybe we want to schedule daily backups, database reindexing, statistics updating, etc. This is why I wanted to have a solution based only on SQL Server 2005 Express and not dependent on the hosting company. And of course there is one based on our old friend the Service Broker.

Friday, December 26, 2008

Double Metaphone Algorithmus in TSQL

Double Metaphone Sounds Great (SQL Magazine)
Convert the C++ Double Metaphone algorithm to T-SQL

26094.zip

Tuesday, August 19, 2008

Common How to UNPIVOT Data Using T-SQL

How to UNPIVOT Data Using T-SQL

Tuesday, August 12, 2008

Fast T-SQL Row Number Generator

TSQL Solutions - Ranking of data values

TSQL Solutions - Concatenating row values in T-SQL

Faking arrays in T-SQL

TSQL Solutions - Creating a table of sequential numbers

TSQL Solutions - Creating a table of sequential numbers

Identity based sequences
Using the default values for an Identity Column in a table, one can simply insert default rows to generate the sequence. One consideration in this approach is that it can be used only with permanent base tables. Note that without any arguments IDENTITY property uses the value 1 for both seed and increment.
CREATE TABLE dbo.Nbrs(n INT NOT NULL IDENTITY) ;
GO
SET NOCOUNT ON ;
INSERT dbo.Nbrs DEFAULT VALUES ;
WHILE SCOPE_IDENTITY() < 500
INSERT dbo.Nbrs DEFAULT VALUES ;

With the IDENTITY()
function one can use SELECT INTO a new table from any existing table. This example demonstrates using a CROSS JOIN between two existing tables.
SELECT TOP 500 IDENTITY(INT) AS n
INTO dbo.Nbrs
FROM Northwind.dbo.Orders o1
CROSS JOIN Northwind.dbo.Orders o2 ;

Sequences based on existing tables
Using an existing base table to view can be an easy way to generate a sequence of numbers. One drawback to such mechanisms is that as the dataset gets larger, the comparative efficiency of the inserts can become an issue. Here is an example using a correlated subquery based on the unique column. SELECT n
FROM ( SELECT ( SELECT COUNT(*)
FROM Northwind.dbo.Orders o2
WHERE o2.OrderId <= o1.OrderId ) AS "n"
FROM Northwind.dbo.Orders o1 ) Nbrs (n)
WHERE n <= 500 ;

Using recursive CTE
A Recursive CTE is common table expression can include references to itself. With an anchor member and a recursive member, a recursive CTE can generate a number listing pretty efficiently. WITH Nbrs ( n ) AS (
SELECT 1 UNION ALL
SELECT 1 + n FROM Nbrs WHERE n < 500 )
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 )

With ROW_NUMBER()
For each row returned in a resultset, ROW_NUMBER function returns a sequential number, starting at 1. One can use any existing table, view or any resultset to generate a ROW_NUMBER() expression. Here is an example:
SELECT n
FROM ( SELECT ROW_NUMBER() OVER ( ORDER BY object_id )
FROM sys.objects ) D ( n )
WHERE n <= 500 ;

As a matter of fact, you can use any arbitrary expression or built-in functions like CURRENT_TIMESTAMP or NEW_ID() in the ORDER BY clause to generate the sequential number. Here is an example re-written using a common table expression
WITH cte ( n ) AS (
SELECT ROW_NUMBER() OVER ( ORDER BY CURRENT_TIMESTAMP )
FROM Northwind.dbo.Orders )
SELECT * FROM cte
WHERE n <= 500 ;

XPATH QUERY SAMPLE: PIX/ASA ISSUES

-- XPATH QUERY - issues by issue type and day
SELECT ISSUE,STUFF((SELECT ', ' + convert(varchar(10),T2.N) + '('+ convert(varchar(3),y)+')'
FROM pix001_issues AS T2 WHERE T2.issue = T1.issue order by y desc
FOR XML PATH ('')), 1, 2,'') AS N
FROM pix001_issues AS T1
group by issue
order by ISSUE
/*
declare @a varchar(8000)
SELECT @a = COALESCE(@a + ',', '') +
CAST(N AS varchar(50))
FROM pix001_issues where issue= 302013 order by y desc
print @a
*/
-- select * from pix001_issues where issue=105006

Result:
ISSUE N
104004 1(192), 1(140)
105003 1(192), 6(191), 1(140), 6(100)
105004 1(192), 6(191), 1(140), 6(100)
105005 1(196), 1(192), 2(176), 1(175), 1(157), 1(141), 1(107), 1(105), 1(81), 1(73), 1(54), 1(35)

Sunday, July 27, 2008

SQL Server Central: Execute T-SQL Asyncronously

Execute T-SQL Asyncronously - SQL Server Central
/*
***** This entire comment portion is not necessarily part of SP, just notes on subject *****

The purpose of this SP is to provide a method in T-SQL to launch another T-SQL thread without having
to wait for its completion, or "local" concern for its correct processing. A good example of this is
when your T-SQL code has finished a certain bit of processing, and now the database is in a "state"
where performing a backup is a good idea, but your current T-SQL code still has some time consuming
work to perform. This SP will allow you to go ahead and "launch" the backup and return immediately
to the current T-SQL code. Another area where this can be used is when a user wants to start a SQL
Server process, but you want the user interface to get back control right away, without having to
wait for the SQL process to complete (this also helps with SQL processes that return timeouts when
called from user interfaces because the process takes longer than the [sometimes difficult to deal
with] user interface timeout configurations.
The basic principal within this SP is to create a SQL Server Agent Job that contains the T-SQL
to perform the desired process. Jobs have a few components. 1. The Job itself is maily just a
named "container" for the Job Step(s) and Job Schedule(s). 2. One or more Job Steps that contain
the actual T-SQL code, and various properties about what happens when the Step is executed.
3. Zero or more Job Schedules (none used in this SP) to be able to configure Jobs to run at a certain
time or interval. The built in SPs that deal with these components are sp_add_job, sp_add_jobstep,
and sp_add_jobschedule, all of which reside in the MSDB database. I encourage you to look at these in
BOL, if just to become familiar with the possibilities that Jobs provide. In Enterprise Manager, access
to these components are at the [Servername]-Management-SQL Server Agent-Jobs branch. Jobs may affect
most all the (stock) tables in the MSDB database, but the major components are in SysJobs, SysJobSteps,
and SysJobSchedules tables. Note: The Job Step @DatabaseName parameter determines the "current" database
to execute the Step's T-SQL code. If I want to execute the same bit of T-SQL for multiple databases, I
simply add more Job Steps to the same Job. IMPORTANT - SQL Server Agent service must be running to have
Jobs execute.
Since I'm not a SQL Server Admin guru, and in my implementaions of this it has not been an issue,
the security context under which the Job Steps will execute may have an impact on whether or not your
T-SQL inside the Job Steps can execute properly. It depends on the security context of the T-SQL code
that calls this SP along with context under which SQL Server Service and/or the SQL Server Agent Service
is being run. See BOL - sp_start_job for more information on this subject.
This SP is a stripped down version of the SP I actually use in production. I have various SPs that
will create and execute Jobs. Some return immediately, while others leverage other aspects of executing
T-SQL code within a Job as opposed to "inline". For example, in the SP code below I test whether the SQL
Agent is running, and if not, simply output an error message. This could easily be modified to go ahead
and execute the passed T-SQL (and wait for its completion) if the Agent is not running. In my production
SP, I "log" calls to this code and test for execution errors etc. Interestingly, a Job will actually
produce records in the MSDB..SysJobHistory table that contain the actual T-SQL PRINT statement outputs,
or SQL error messages you generally see in the text output window wihtin Query Analyzer. I use this
in some data driven debugging to be able to have the actual SQL error messages available as opposed to
inline code dealing with just the @@Error codes. This data can also be output to O/S text files. Only
one minor problem, they are unicode.
When calling this SP, your expectation should be a return to the calling code within a fraction of a
second. I would not recommnend calling this many times within a loop or something. It would not only be
a fairly slow loop, but, all of a sudden SQL Server will have many, many threads (SPIDS) running at the
same time. This may be a great testing methodology in some instances, but should not be done in a
production environment. On a "quiet" SQL Sever, the Job this SP creates should start executing within a
fraction of a second. If many Jobs are aleady executing, it may take seconds or longer for Jobs to start.
I have issued a hundred or so calls in a loop for testing, and have observed within Enterprise Manager--Jobs
how the jobs start execute and end. I have not seen any affect on the performance of T-SQL code while
being executed from within a job.
This SP creates uniquely named jobs. If the passed T-SQL executes without an error, the Job will delete
itself. This also means, if there is an error, the Job this SP creates will remain. It must then be either
manually deleted, or executed successfully to be removed. If the Job does fail, view the Job History from
within Enterprise Manager (check the "Show Step Details").

If you notice, the 1st part of the SP has an IF with a bunch of "constant" text. I use this as a way to
self-document my SPs. If the SP requires a parameter, and it is not supplied, I treat the SP call as if
someone had typed "SyExecSQLASyncSP /?" or something, and PRINT out the documentation of the SP. This has
become a practice of mine and is very usefull to me (since I can't remember squat anymore). Often, in
Query Analyzer I see a call to an SP, I just simply double-click on it to select it, and hit F5 to run it.
It then outputs it's own documentation and refreshes my memory of it's usage or purpose.

I'll mention it again. I highly recommend looking up the information in BOL about the commands used in this SP.

***** End Of - This entire comment portion is not necessarily part of SP, just notes on subject *****
*/


Create Procedure SyExecSQLASyncSP
 @Command VarChar(3200) = NULL
AS

IF @Command IS NULL BEGIN -- Self Doc Section
 PRINT 'Executes passed T-SQL batch ASyncronously by creating and then running a SQL Server Job.

EXEC SyExecSQLASyncSP  [@Command = ''T-SQL script'']

Parameter:
 @Command Up to 3200 character string of T-SQL code

Example Call:
 EXEC SyExecSQLASyncSP ''EXEC SomeSP'' 

or to execute something in 5 minutes, but return control right away
 EXEC SyExecSQLASyncSP ''WAITFOR DELAY ''''00:05:00'''' EXEC SomeSP'' 
 '
 RETURN
END -- End Self Doc Section

DECLARE @JobID UNIQUEIDENTIFIER,
 @JName VarChar(128),
 @JDesc VarChar(512),
 @Now VarChar(30),
 @sDBName VarChar(128),
 @Note VarChar(8000)

-- Check IF Agent is running
IF (SELECT count(*) FROM Master.dbo.SysProcesses WHERE Program_Name = 'SQLAgent - Generic Refresher') = 0 BEGIN
 SET @Note = 'Errors occured in ASync request. SQL Server Agent is NOT running.' + Char(13) + Char(10) +
  'Batch command request:' + @Command
 RAISERROR (@Note, 0, 1) With NoWait
 RETURN
END

SET @sDBName = DB_NAME(DB_ID())

-- Create a Job to run passed SQL batch
SET @Now = (SELECT convert(VarChar,getdate()) + ':' + convert(VarChar,DATEPART(s, getdate())) + ':' + convert(VarChar,DATEPART(ms, getdate())) )
SET @JName = 'Temp Job (ASync DB:' + @sDBName + ') ' + @Now
SET @JDesc = 'Temp Job to run command ASyncronously on database:' + @sDBName
EXEC msdb..sp_add_job
 @job_name = @JName,
 @enabled = 1,
 @description = @JDesc,
 @delete_level = 1,  -- delete job on completion
 @job_id = @JobID OUTPUT

-- Add target server to job
EXEC msdb..sp_add_jobserver @job_id = @JobID, @server_name = @@SERVERNAME

-- Create  step 1, Actual command
EXEC msdb..sp_add_jobstep
 @job_id = @JobID,
 @step_name = 'Run T-SQL ASyncronously',
 @subsystem = 'TSQL',
 @command = @Command,
 @database_name = @sDBName

-- Execute the Job
EXEC msdb..sp_start_job @job_name = @JName, @error_flag = Null, @server_name  = Null, @step_name = Null, @output_flag = 0

SQL Server Central: URLEncode in TSQL

URLEncode - SQL Server Central
Call this function as you would any scalar UDF:
select dbo.URLEncode('K8%/fwO3L mEQ*.}')
This script requires a numbers tabel and assumes the following schema:
CREATE TABLE dbo.Numbers
(
Num INT NOT NULL
CONSTRAINT [PKC__Numbers__Num]
PRIMARY KEY CLUSTERED (Num) on [PRIMARY]
)

GO
SET ANSI_NULLS ON
GO

IF EXISTS (
SELECT 1
FROM dbo.sysobjects 
WHERE id = OBJECT_ID(N'[dbo].[URLEncode]') 
AND xtype IN (N'FN', N'IF', N'TF'))
BEGIN
DROP FUNCTION [dbo].[URLEncode]
END
GO

CREATE FUNCTION [dbo].[URLEncode] 
(@decodedString VARCHAR(4000))
RETURNS VARCHAR(4000)
AS
BEGIN
/*******************************************************************************************************
*   dbo.URLEncode 
*   Creator:       Robert Cary
*   Date:          03/18/2008
*
*   Notes:         
*                  
*
*   Usage:
select dbo.URLEncode('K8%/fwO3L mEQ*.}')
*   Modifications:   
*   Developer Name      Date        Brief description
*   ------------------- ----------- ------------------------------------------------------------
*   
********************************************************************************************************/

DECLARE @encodedString VARCHAR(4000)

IF @decodedString LIKE '%[^a-zA-Z0-9*-.!_]%' ESCAPE '!'
BEGIN
SELECT @encodedString = REPLACE(
COALESCE(@encodedString, @decodedString),
SUBSTRING(@decodedString,num,1),
'%' + SUBSTRING(master.dbo.fn_varbintohexstr(CONVERT(VARBINARY(1),ASCII(SUBSTRING(@decodedString,num,1)))),3,3))
FROM dbo.numbers 
WHERE num BETWEEN 1 AND LEN(@decodedString) AND SUBSTRING(@decodedString,num,1) like '[^a-zA-Z0-9*-.!_]' ESCAPE '!'
END
ELSE
BEGIN
SELECT @encodedString = @decodedString 
END

RETURN @encodedString

END
GO

SQL Server Central: sp_what

sp_what - SQL Server Central
This is a replacement for SP_who and modification of sp_who2.

It
will always list only active, nonsystem processes, and will list the
number of seconds a transaction has been running for. Some transactions
do not report a last batch time, so I forced a large value to display
so you can see any issue with that transaction. Its sorted by spid, so
you can se more easily whats blocking, and the columns are arranged to
fit all info better on the screen (1280x1024).

CREATE PROCEDURE sp_what  --- 2006/3/22
--    @loginame     sysname = NULL
as

set nocount on

declare
@retcode         int, @loginame  sysname
declare
@sidlow varbinary(85),@sidhigh varbinary(85) ,@sid1 varbinary(85) ,@spidlow int ,@spidhigh int

declare
@charMaxLenLoginName varchar(6),@charMaxLenDBName varchar(6),@charMaxLenCPUTime varchar(10),@charMaxLenDiskIO varchar(10),
@charMaxLenHostName varchar(10),@charMaxLenProgramName varchar(10),@charMaxLenLastBatch varchar(10),@charMaxLenCommand varchar(10)
--------
select  @retcode         = 0      -- 0=good ,1=bad.
--------------------------------------------------------------

if (object_id('tempdb..#tb1_sysprocesses') is not null)
drop table #tb1_sysprocesses

--------------------  Capture consistent sysprocesses.  -------------------

SELECT
spid,status,sid,hostname,program_name,cmd,cpu,physical_io,blocked,dbid,
convert(sysname, rtrim(loginame)) as loginname,spid as 'spid_sort', 
substring( convert(varchar,last_batch,111) ,6  ,5 ) + ' ' + substring( convert(varchar,last_batch,113) ,13 ,8 ) as 'last_batch_char',last_batch
INTO    #tb1_sysprocesses
from master.dbo.sysprocesses   (nolock)

--------Screen out any rows
DELETE #tb1_sysprocesses
where   lower(status)  = 'sleeping'
and     upper(cmd)    IN (
'AWAITING COMMAND'
,'MIRROR HANDLER'
,'LAZY WRITER'
,'CHECKPOINT SLEEP'
,'RA MANAGER'
,'TASK MANAGER'
)

and     blocked       = 0 or spid <= 50
---set the column widths
UPDATE #tb1_sysprocesses set last_batch = DATEADD(year,-10,GETDATE()) 
where last_batch IS NULL or last_batch = '01/01/1901 00:00:00' or last_batch < '01/01/1950'
update #tb1_sysprocesses set status = substring(status,1,10), program_name = substring(program_name,1,20)
ALTER TABLE #tb1_sysprocesses 
ALTER COLUMN status varchar(10)
ALTER TABLE #tb1_sysprocesses 
ALTER COLUMN program_name varchar(20)
--------Prepare to dynamically optimize column widths.
SELECT
@charMaxLenLoginName = convert( varchar ,isnull( max( datalength(loginname)) ,5)),
@charMaxLenDBName = convert( varchar ,isnull( max( datalength( rtrim(convert(varchar(128),db_name(dbid))))) ,6)),
@charMaxLenCPUTime =  convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),cpu)))) ,7)),
@charMaxLenDiskIO =  convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),physical_io)))) ,6)),
@charMaxLenCommand = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),cmd)))) ,7)),
@charMaxLenHostName  = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),hostname)))) ,8)),
@charMaxLenProgramName = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),program_name)))) ,11)),
@charMaxLenLastBatch = convert( varchar,isnull( max( datalength( rtrim(convert(varchar(128),last_batch_char)))) ,9))
from
#tb1_sysprocesses
where
spid >= 0 and spid <= 32767



--------Output the report.


EXECUTE(
'SET nocount off
SELECT   SPID          = convert(char(5),spid)
,HostName      =
CASE hostname
When Null  Then ''  .''
When '' '' Then ''  .''
Else    substring(hostname,1,' + @charMaxLenHostName + ')
END
,BlkBy         =
CASE               isnull(convert(char(5),blocked),''0'')
When ''0'' Then ''  .''
Else            isnull(convert(char(5),blocked),''0'')
END
,ActiveSeconds = DATEDIFF(ss,last_batch,getdate())
,DBName        = substring(case when dbid = 0 then null when dbid <> 0 then db_name(dbid) end,1,' + @charMaxLenDBName + ')
,Command       = substring(cmd,1,' + @charMaxLenCommand + ')
,Status        =
CASE lower(status)
When ''sleeping'' Then lower(status)
Else                   upper(status)
END
,BatchStart = CONVERT(varchar(8),last_batch,14)
,Now = CONVERT(varchar(8),getdate(),14)
,LBDate = substring(last_batch_char,1,5)
,ProgramName   = substring(program_name,1,' + @charMaxLenProgramName + ')
,Login         = substring(loginname,1,' + @charMaxLenLoginName + ')
,CPUTime       = substring(convert(varchar,cpu),1,' + @charMaxLenCPUTime + ')
,DiskIO        = substring(convert(varchar,physical_io),1,' + @charMaxLenDiskIO + ')
from
#tb1_sysprocesses  --Usually DB qualification is needed in exec().
order by CAST(SPID as int)
-- (Seems always auto sorted.)   order by SPID
SET nocount on')

drop table #tb1_sysprocesses
--return @retcode 
GO

SQL Server Central: Updating all the indexes/statistics of a database

Updating all the indexes/statistics of a database - SQL Server Central

sp_MSForEachTable "DBCC DBREINDEX('?')"

The below command is for updating the statistics with full scan.
We also need to run this command in the context of each database.

sp_MSForEachTable  "UPDATE STATISTICS ? with fullscan"

SQL Server Central: Update statistics for all tables in any DB

Update statistics for all tables in any DB - SQL Server Central

USE pubs -- Change desired database name here
GO
SET NOCOUNT ON
GO
DECLARE updatestats CURSOR FOR
SELECT table_name FROM information_schema.tables
where TABLE_TYPE = 'BASE TABLE'
OPEN updatestats

DECLARE @tablename NVARCHAR(128)
DECLARE @Statement NVARCHAR(300)

FETCH NEXT FROM updatestats INTO @tablename
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT N'UPDATING STATISTICS ' + @tablename
SET @Statement = 'UPDATE STATISTICS '  + @tablename + '  WITH FULLSCAN'
EXEC sp_executesql @Statement
FETCH NEXT FROM updatestats INTO @tablename
END

CLOSE updatestats
DEALLOCATE updatestats
GO
SET NOCOUNT OFF
GO

SQL Server Central: SQL Server 2005: Script all Indexes

SQL Server 2005: Script all Indexes - SQL Server Central

-- Get all existing indexes, but NOT the primary keys
DECLARE cIX CURSOR FOR
SELECT OBJECT_NAME(SI.Object_ID), SI.Object_ID, SI.Name, SI.Index_ID
FROM Sys.Indexes SI 
LEFT JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC ON SI.Name = TC.CONSTRAINT_NAME AND OBJECT_NAME(SI.Object_ID) = TC.TABLE_NAME
WHERE TC.CONSTRAINT_NAME IS NULL
AND OBJECTPROPERTY(SI.Object_ID, 'IsUserTable') = 1
ORDER BY OBJECT_NAME(SI.Object_ID), SI.Index_ID

DECLARE @IxTable SYSNAME
DECLARE @IxTableID INT
DECLARE @IxName SYSNAME
DECLARE @IxID INT

-- Loop through all indexes
OPEN cIX
FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE @IXSQL NVARCHAR(4000) SET @PKSQL = ''
SET @IXSQL = 'CREATE '

-- Check if the index is unique
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsUnique') = 1)
SET @IXSQL = @IXSQL + 'UNIQUE '
-- Check if the index is clustered
IF (INDEXPROPERTY(@IxTableID, @IxName, 'IsClustered') = 1)
SET @IXSQL = @IXSQL + 'CLUSTERED '

SET @IXSQL = @IXSQL + 'INDEX ' + @IxName + ' ON ' + @IxTable + '('

-- Get all columns of the index
DECLARE cIxColumn CURSOR FOR 
SELECT SC.Name
FROM Sys.Index_Columns IC
JOIN Sys.Columns SC ON IC.Object_ID = SC.Object_ID AND IC.Column_ID = SC.Column_ID
WHERE IC.Object_ID = @IxTableID AND Index_ID = @IxID
ORDER BY IC.Index_Column_ID

DECLARE @IxColumn SYSNAME
DECLARE @IxFirstColumn BIT SET @IxFirstColumn = 1

-- Loop throug all columns of the index and append them to the CREATE statement
OPEN cIxColumn
FETCH NEXT FROM cIxColumn INTO @IxColumn
WHILE (@@FETCH_STATUS = 0)
BEGIN
IF (@IxFirstColumn = 1)
SET @IxFirstColumn = 0
ELSE
SET @IXSQL = @IXSQL + ', '

SET @IXSQL = @IXSQL + @IxColumn

FETCH NEXT FROM cIxColumn INTO @IxColumn
END
CLOSE cIxColumn
DEALLOCATE cIxColumn

SET @IXSQL = @IXSQL + ')'
-- Print out the CREATE statement for the index
PRINT @IXSQL

FETCH NEXT FROM cIX INTO @IxTable, @IxTableID, @IxName, @IxID
END

CLOSE cIX
DEALLOCATE cIX

SQL Server Central: Prioritize Missing Index Recommendations (2005)

Prioritize Missing Index Recommendations (2005) - SQL Server Central

-- ---------------------------------------------------------------------------------------------------
--  Author:  Michael Smith, Minneapolis, MN
--  Date:    2007-08-17
--  
--  Purpose: To report indexes proposed by the database engine that have highest probable user impact.
--    Note that no consideration is given to 'reasonableness' of indexes-- bytes, overall size, total
--    number of indexes on a table, etc.  Intended to provide targeted starting point for holistic 
--    evaluation of indexes.
--  
--  
--  Directions:  Specify running total percent impact threshold and minimum number or results.
--  
--  
--  Many thanks to Itzik Ben-Gan for the query technique and pattern as discussed in his book,
--  Inside SQL Server 2005: T-SQL Querying.  Also, the "impact formula" is taken from SQL Server
--  Books Online [cost * impact * (scans + seeks)].
-- ---------------------------------------------------------------------------------------------------



SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
GO

DECLARE @percent_lvl  int;
DECLARE @min_rows int;

SET @percent_lvl = 50;
SET @min_rows = 20;


WITH missing_index_impact AS (
SELECT 
dm_db_missing_index_groups.index_handle,
SUM(
(dm_db_missing_index_group_stats.avg_total_user_cost * dm_db_missing_index_group_stats.avg_user_impact *
(dm_db_missing_index_group_stats.user_seeks + dm_db_missing_index_group_stats.user_scans))
) AS "total_impact",
(100.00 *
SUM(dm_db_missing_index_group_stats.avg_total_user_cost * dm_db_missing_index_group_stats.avg_user_impact *
(dm_db_missing_index_group_stats.user_seeks + dm_db_missing_index_group_stats.user_scans)) /
SUM(SUM(dm_db_missing_index_group_stats.avg_total_user_cost *
dm_db_missing_index_group_stats.avg_user_impact *
(dm_db_missing_index_group_stats.user_seeks + dm_db_missing_index_group_stats.user_scans))) OVER()
) AS "percent_impact",
ROW_NUMBER() OVER(ORDER BY SUM(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)) DESC ) AS rn
FROM sys.dm_db_missing_index_groups AS dm_db_missing_index_groups
JOIN sys.dm_db_missing_index_group_stats AS dm_db_missing_index_group_stats
ON dm_db_missing_index_groups.index_group_handle = dm_db_missing_index_group_stats.group_handle
GROUP
BY dm_db_missing_index_groups.index_handle),
agg_missing_index_impact AS (
SELECT missing_index_impact_1.index_handle,
missing_index_impact_1.total_impact,
SUM(missing_index_impact_2.total_impact) AS running_total_impact,
missing_index_impact_1.percent_impact,
SUM(missing_index_impact_2.percent_impact) AS running_total_percent,
missing_index_impact_1.rn
FROM missing_index_impact AS missing_index_impact_1
JOIN missing_index_impact AS missing_index_impact_2
ON missing_index_impact_1.rn <= missing_index_impact_2.rn
GROUP
BY missing_index_impact_1.index_handle, missing_index_impact_1.total_impact,
missing_index_impact_1.percent_impact, missing_index_impact_1.rn
HAVING SUM(missing_index_impact_2.percent_impact) - missing_index_impact_1.percent_impact >= @percent_lvl
OR missing_index_impact_1.rn <= @min_rows
),
missing_index_details AS (
SELECT dm_db_missing_index_details.index_handle,
dm_db_missing_index_details."statement",
dm_db_missing_index_details.equality_columns,
dm_db_missing_index_details.inequality_columns,
dm_db_missing_index_details.included_columns
FROM sys.dm_db_missing_index_details AS dm_db_missing_index_details
)

SELECT agg_missing_index_impact.rn,
missing_index_details."statement",
agg_missing_index_impact.running_total_impact,
agg_missing_index_impact.total_impact,
agg_missing_index_impact.running_total_percent,
agg_missing_index_impact.percent_impact,
missing_index_details.equality_columns,
missing_index_details.inequality_columns,
missing_index_details.included_columns
FROM agg_missing_index_impact
JOIN missing_index_details
ON agg_missing_index_impact.index_handle = missing_index_details.index_handle
ORDER
BY agg_missing_index_impact.rn ASC;
-- ---------------------------------------------------------------------------------------------------
--  Author:  Michael Smith, Minneapolis, MN
--  Date:    2007-08-17
--  
--  Purpose: To report indexes proposed by the database engine that have highest probable user impact.
--    Note that no consideration is given to 'reasonableness' of indexes-- bytes, overall size, total
--    number of indexes on a table, etc.  Intended to provide targeted starting point for holistic 
--    evaluation of indexes.
--  
--  
--  Directions:  Specify running total percent impact threshold and minimum number or results.
--  
--  
--  Many thanks to Itzik Ben-Gan for the query technique and pattern as discussed in his book,
--  Inside SQL Server 2005: T-SQL Querying.  Also, the "impact formula" is taken from SQL Server
--  Books Online [cost * impact * (scans + seeks)].
-- ---------------------------------------------------------------------------------------------------



SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
GO

DECLARE @percent_lvl  int;
DECLARE @min_rows int;

SET @percent_lvl = 50;
SET @min_rows = 20;


WITH missing_index_impact AS (
SELECT 
dm_db_missing_index_groups.index_handle,
SUM(
(dm_db_missing_index_group_stats.avg_total_user_cost * dm_db_missing_index_group_stats.avg_user_impact *
(dm_db_missing_index_group_stats.user_seeks + dm_db_missing_index_group_stats.user_scans))
) AS "total_impact",
(100.00 *
SUM(dm_db_missing_index_group_stats.avg_total_user_cost * dm_db_missing_index_group_stats.avg_user_impact *
(dm_db_missing_index_group_stats.user_seeks + dm_db_missing_index_group_stats.user_scans)) /
SUM(SUM(dm_db_missing_index_group_stats.avg_total_user_cost *
dm_db_missing_index_group_stats.avg_user_impact *
(dm_db_missing_index_group_stats.user_seeks + dm_db_missing_index_group_stats.user_scans))) OVER()
) AS "percent_impact",
ROW_NUMBER() OVER(ORDER BY SUM(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)) DESC ) AS rn
FROM sys.dm_db_missing_index_groups AS dm_db_missing_index_groups
JOIN sys.dm_db_missing_index_group_stats AS dm_db_missing_index_group_stats
ON dm_db_missing_index_groups.index_group_handle = dm_db_missing_index_group_stats.group_handle
GROUP
BY dm_db_missing_index_groups.index_handle),
agg_missing_index_impact AS (
SELECT missing_index_impact_1.index_handle,
missing_index_impact_1.total_impact,
SUM(missing_index_impact_2.total_impact) AS running_total_impact,
missing_index_impact_1.percent_impact,
SUM(missing_index_impact_2.percent_impact) AS running_total_percent,
missing_index_impact_1.rn
FROM missing_index_impact AS missing_index_impact_1
JOIN missing_index_impact AS missing_index_impact_2
ON missing_index_impact_1.rn <= missing_index_impact_2.rn
GROUP
BY missing_index_impact_1.index_handle, missing_index_impact_1.total_impact,
missing_index_impact_1.percent_impact, missing_index_impact_1.rn
HAVING SUM(missing_index_impact_2.percent_impact) - missing_index_impact_1.percent_impact >= @percent_lvl
OR missing_index_impact_1.rn <= @min_rows
),
missing_index_details AS (
SELECT dm_db_missing_index_details.index_handle,
dm_db_missing_index_details."statement",
dm_db_missing_index_details.equality_columns,
dm_db_missing_index_details.inequality_columns,
dm_db_missing_index_details.included_columns
FROM sys.dm_db_missing_index_details AS dm_db_missing_index_details
)

SELECT agg_missing_index_impact.rn,
missing_index_details."statement",
agg_missing_index_impact.running_total_impact,
agg_missing_index_impact.total_impact,
agg_missing_index_impact.running_total_percent,
agg_missing_index_impact.percent_impact,
missing_index_details.equality_columns,
missing_index_details.inequality_columns,
missing_index_details.included_columns
FROM agg_missing_index_impact
JOIN missing_index_details
ON agg_missing_index_impact.index_handle = missing_index_details.index_handle
ORDER
BY agg_missing_index_impact.rn ASC;

SQL Server Central: Prioritize Missing Index Recommendations 2 (2005)

Prioritize Missing Index Recommendations 2 (2005) - SQL Server Central

-- ---------------------------------------------------------------------------------------------------
--  Author:  Michael Smith, Minneapolis, MN
--  Date:    2007-08-17
--  
--  Purpose: To report indexes proposed by the database engine that have highest probable user impact.
--  
--  Gives counts of columns, plus the bytes of columns (key columns and total) to assist in determining
--  if recommended index is a reasonable index to implement.
-- ---------------------------------------------------------------------------------------------------

SET ANSI_NULLS ON;
SET ANSI_PADDING ON;
SET ANSI_WARNINGS ON;
SET ARITHABORT ON;
SET CONCAT_NULL_YIELDS_NULL ON;
SET NUMERIC_ROUNDABORT OFF;
SET QUOTED_IDENTIFIER ON;
SET NOCOUNT ON;
GO

WITH missing_index_impact AS (
SELECT 
dm_db_missing_index_groups.index_handle,
SUM(
(dm_db_missing_index_group_stats.avg_total_user_cost * dm_db_missing_index_group_stats.avg_user_impact *
(dm_db_missing_index_group_stats.user_seeks + dm_db_missing_index_group_stats.user_scans))
) AS "total_impact",
(100.00 *
SUM(dm_db_missing_index_group_stats.avg_total_user_cost * dm_db_missing_index_group_stats.avg_user_impact *
(dm_db_missing_index_group_stats.user_seeks + dm_db_missing_index_group_stats.user_scans)) /
SUM(SUM(dm_db_missing_index_group_stats.avg_total_user_cost *
dm_db_missing_index_group_stats.avg_user_impact *
(dm_db_missing_index_group_stats.user_seeks + dm_db_missing_index_group_stats.user_scans))) OVER()
) AS "percent_impact",
ROW_NUMBER() OVER(ORDER BY SUM(avg_total_user_cost * avg_user_impact * (user_seeks + user_scans)) DESC ) AS rn
FROM sys.dm_db_missing_index_groups AS dm_db_missing_index_groups
JOIN sys.dm_db_missing_index_group_stats AS dm_db_missing_index_group_stats
ON dm_db_missing_index_groups.index_group_handle = dm_db_missing_index_group_stats.group_handle
GROUP
BY dm_db_missing_index_groups.index_handle),
agg_missing_index_impact AS (
SELECT missing_index_impact_1.index_handle,
missing_index_impact_1.total_impact,
SUM(missing_index_impact_2.total_impact) AS running_total_impact,
missing_index_impact_1.percent_impact,
SUM(missing_index_impact_2.percent_impact) AS running_total_percent,
missing_index_impact_1.rn
FROM missing_index_impact AS missing_index_impact_1
JOIN missing_index_impact AS missing_index_impact_2
ON missing_index_impact_1.rn <= missing_index_impact_2.rn
GROUP
BY missing_index_impact_1.index_handle, missing_index_impact_1.total_impact,
missing_index_impact_1.percent_impact, missing_index_impact_1.rn
),
missing_index_details AS (
SELECT dm_db_missing_index_details."object_id",
dm_db_missing_index_details.index_handle,
dm_db_missing_index_details."statement",
dm_db_missing_index_details.equality_columns,
dm_db_missing_index_details.inequality_columns,
dm_db_missing_index_details.included_columns
FROM sys.dm_db_missing_index_details AS dm_db_missing_index_details
)

SELECT agg_missing_index_impact.rn,
missing_index_details."statement",
agg_missing_index_impact.running_total_impact,
agg_missing_index_impact.total_impact,
agg_missing_index_impact.running_total_percent,
agg_missing_index_impact.percent_impact,
missing_index_details.equality_columns,
missing_index_details.inequality_columns,
missing_index_details.included_columns,
"key".index_key_column_count,
"key".index_key_column_bytes,
"all".index_all_column_count,
"all".index_all_column_bytes
FROM agg_missing_index_impact AS agg_missing_index_impact
JOIN missing_index_details AS missing_index_details
ON agg_missing_index_impact.index_handle = missing_index_details.index_handle
JOIN   (
SELECT  missing_index_details1.index_handle,
COUNT(*) AS index_key_column_count,
SUM(COL_LENGTH(missing_index_details1."statement", dm_db_missing_index_columns1.column_name )) AS index_key_column_bytes
FROM    missing_index_details AS missing_index_details1
CROSS
APPLY   sys.dm_db_missing_index_columns (missing_index_details1.index_handle) AS dm_db_missing_index_columns1
WHERE   dm_db_missing_index_columns1.column_usage = 'EQUALITY'
OR   dm_db_missing_index_columns1.column_usage = 'INEQUALITY'
GROUP
BY   missing_index_details1.index_handle
) AS "key"
ON    missing_index_details.index_handle = "key".index_handle
JOIN   (
SELECT  missing_index_details2.index_handle,
COUNT(*) AS index_all_column_count,
SUM(COL_LENGTH(missing_index_details2."statement", dm_db_missing_index_columns2.column_name )) AS index_all_column_bytes
FROM    missing_index_details AS missing_index_details2
CROSS
APPLY   sys.dm_db_missing_index_columns (missing_index_details2.index_handle) AS dm_db_missing_index_columns2
GROUP
BY   missing_index_details2.index_handle
) AS "all"
ON    missing_index_details.index_handle = "all".index_handle
ORDER
BY agg_missing_index_impact.rn ASC;

SQL Server Central: SQL Server 2005 - Backup, Integrity Check and Index Optimization

SQL Server 2005 - Backup, Integrity Check and Index Optimization - SQL Server Central
I have made a solution for backup, integrity check and index
optimization in SQL Server 2005. The solution is based on stored
procedures, functions, sqlcmd and SQL Server Agent jobs.

Dynamic selection of databases, e.g. USER_DATABASES.
Database state check. If a database is not online the procedure logs the database state and continues to the next database.
Robust
error handling and logging. If an error occurs the procedure logs the
error and continues to the next database or index. In the end the job
reports failure. Information about all commands are logged with start
time, command text, command output and end time.
Database
backup features. Full, differential and transaction log backups.
Automatic creation of backup directories. Backup file names with the
name of the instance, the name of the database, backup type, date and
time. Verification of backups. Deletion of old backup files.

EXECUTE dbo.DatabaseBackup
@Databases = 'USER_DATABASES',
@Directory = 'C:\Backup',
@BackupType = 'FULL',
@Verify = 'Y',
@CleanupTime = 24
Database integrity check features.

EXECUTE dbo.DatabaseIntegrityCheck
@Databases = 'USER_DATABASES'
Dynamic
index optimization. Rebuild indexes online or offline, reorganize
indexes, update statistics, reorganize indexes and update statistics or
do nothing based on fragmentation level and lob existence.

EXECUTE dbo.IndexOptimize
@Databases = 'USER_DATABASES',
@FragmentationHigh_LOB = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh_NonLOB = 'INDEX_REBUILD_ONLINE',
@FragmentationMedium_LOB = 'INDEX_REORGANIZE',
@FragmentationMedium_NonLOB = 'INDEX_REORGANIZE',
@FragmentationLow_LOB = 'NOTHING',
@FragmentationLow_NonLOB = 'NOTHING',
@FragmentationLevel1 = 5,
@FragmentationLevel2 = 30,
@PageCountLevel = 1000

Please see the code and the documentation.

Ola Hallengren
http://ola.hallengren.com

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[DatabaseSelect] (@DatabaseList varchar(max))

RETURNS @Database TABLE(DatabaseName varchar(max) NOT NULL)

AS

BEGIN

DECLARE @Database01 TABLE( DatabaseName varchar(max),
DatabaseStatus bit)

DECLARE @Database02 TABLE( DatabaseName varchar(max),
DatabaseStatus bit)

DECLARE @DatabaseItem varchar(max)
DECLARE @Position int

SET @DatabaseList = LTRIM(RTRIM(@DatabaseList))
SET @DatabaseList = REPLACE(@DatabaseList,' ','')
SET @DatabaseList = REPLACE(@DatabaseList,'[','')
SET @DatabaseList = REPLACE(@DatabaseList,']','')
SET @DatabaseList = REPLACE(@DatabaseList,'''','')
SET @DatabaseList = REPLACE(@DatabaseList,'"','')

WHILE CHARINDEX(',,',@DatabaseList) > 0 SET @DatabaseList = REPLACE(@DatabaseList,',,',',')

IF RIGHT(@DatabaseList,1) = ',' SET @DatabaseList = LEFT(@DatabaseList,LEN(@DatabaseList) - 1)
IF LEFT(@DatabaseList,1) = ',' SET @DatabaseList = RIGHT(@DatabaseList,LEN(@DatabaseList) - 1)

WHILE LEN(@DatabaseList) > 0
BEGIN
SET @Position = CHARINDEX(',', @DatabaseList)
IF @Position = 0
BEGIN
SET @DatabaseItem = @DatabaseList
SET @DatabaseList = ''
END
ELSE
BEGIN
SET @DatabaseItem = LEFT(@DatabaseList, @Position - 1) 
SET @DatabaseList = RIGHT(@DatabaseList, LEN(@DatabaseList) - @Position)
END
INSERT INTO @Database01 (DatabaseName) VALUES(@DatabaseItem)
END

UPDATE @Database01
SET DatabaseStatus = 1
WHERE DatabaseName NOT LIKE '-%'

UPDATE @Database01
SET DatabaseName = RIGHT(DatabaseName,LEN(DatabaseName) - 1), DatabaseStatus = 0
WHERE DatabaseName LIKE '-%'

INSERT INTO @Database02 (DatabaseName, DatabaseStatus)
SELECT DISTINCT DatabaseName, DatabaseStatus
FROM @Database01
WHERE DatabaseName NOT IN('SYSTEM_DATABASES','USER_DATABASES')

IF EXISTS (SELECT * FROM @Database01 WHERE DatabaseName = 'SYSTEM_DATABASES' AND DatabaseStatus = 0)
BEGIN
INSERT INTO @Database02 (DatabaseName, DatabaseStatus) VALUES('master', 0)
INSERT INTO @Database02 (DatabaseName, DatabaseStatus) VALUES('model', 0)
INSERT INTO @Database02 (DatabaseName, DatabaseStatus) VALUES('msdb', 0)
END

IF EXISTS (SELECT * FROM @Database01 WHERE DatabaseName = 'SYSTEM_DATABASES' AND DatabaseStatus = 1)
BEGIN
INSERT INTO @Database02 (DatabaseName, DatabaseStatus) VALUES('master', 1)
INSERT INTO @Database02 (DatabaseName, DatabaseStatus) VALUES('model', 1)
INSERT INTO @Database02 (DatabaseName, DatabaseStatus) VALUES('msdb', 1)
END

IF EXISTS (SELECT * FROM @Database01 WHERE DatabaseName = 'USER_DATABASES' AND DatabaseStatus = 0)
BEGIN
INSERT INTO @Database02 (DatabaseName, DatabaseStatus)
SELECT [name], 0
FROM sys.databases
WHERE database_id > 4
END

IF EXISTS (SELECT * FROM @Database01 WHERE DatabaseName = 'USER_DATABASES' AND DatabaseStatus = 1)
BEGIN
INSERT INTO @Database02 (DatabaseName, DatabaseStatus)
SELECT [name], 1
FROM sys.databases
WHERE database_id > 4
END

INSERT INTO @Database (DatabaseName)
SELECT [name]
FROM sys.databases
WHERE [name] <> 'tempdb'
INTERSECT
SELECT DatabaseName
FROM @Database02
WHERE DatabaseStatus = 1
EXCEPT
SELECT DatabaseName
FROM @Database02
WHERE DatabaseStatus = 0

RETURN

END
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[CommandExecute]

@Command varchar(max),
@Comment varchar(max),
@Mode int

AS

SET NOCOUNT ON

SET LOCK_TIMEOUT 3600000

----------------------------------------------------------------------------------------------------
--// Declare variables //--
----------------------------------------------------------------------------------------------------

DECLARE @StartMessage varchar(max)
DECLARE @EndMessage varchar(max)
DECLARE @ErrorMessage varchar(max)

DECLARE @Error int

SET @Error = 0

----------------------------------------------------------------------------------------------------
--// Check input parameters //--
----------------------------------------------------------------------------------------------------

IF @Command IS NULL OR @Command = ''
BEGIN
SET @ErrorMessage = 'The value for parameter @Command is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

IF @Comment IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @Comment is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

IF @Mode NOT IN(1,2) OR @Mode IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @Mode is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

----------------------------------------------------------------------------------------------------
--// Check error variable //--
----------------------------------------------------------------------------------------------------

IF @Error <> 0 GOTO ReturnCode

----------------------------------------------------------------------------------------------------
--// Log initial information //--
----------------------------------------------------------------------------------------------------

SET @StartMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Command: ' + @Command + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Comment: ' + @Comment

RAISERROR(@StartMessage,10,1) WITH NOWAIT

----------------------------------------------------------------------------------------------------
--// Execute command //--
----------------------------------------------------------------------------------------------------

IF @Mode = 1
BEGIN
EXECUTE(@Command)
SET @Error = @@ERROR
END

IF @Mode = 2
BEGIN
BEGIN TRY
EXECUTE(@Command)
END TRY
BEGIN CATCH
SET @Error = ERROR_NUMBER()
SET @ErrorMessage = 'Msg ' + CAST(ERROR_NUMBER() AS varchar) + ', ' + ISNULL(ERROR_MESSAGE(),'')
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
END CATCH
END

----------------------------------------------------------------------------------------------------
--// Log completing information //--
----------------------------------------------------------------------------------------------------

SET @EndMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)

RAISERROR(@EndMessage,10,1) WITH NOWAIT

----------------------------------------------------------------------------------------------------
--// Return code //--
----------------------------------------------------------------------------------------------------

ReturnCode:

RETURN @Error

----------------------------------------------------------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DatabaseBackup]

@Databases varchar(max),
@Directory varchar(max),
@BackupType varchar(max),
@Verify varchar(max),
@CleanupTime int

AS

SET NOCOUNT ON

----------------------------------------------------------------------------------------------------
--// Declare variables //--
----------------------------------------------------------------------------------------------------

DECLARE @StartMessage varchar(max)
DECLARE @EndMessage varchar(max)
DECLARE @DatabaseMessage varchar(max)
DECLARE @ErrorMessage varchar(max)

DECLARE @InstanceName varchar(max)
DECLARE @FileExtension varchar(max)

DECLARE @CurrentID int
DECLARE @CurrentDatabase varchar(max)
DECLARE @CurrentDirectory varchar(max)
DECLARE @CurrentDate varchar(max)
DECLARE @CurrentFileName varchar(max)
DECLARE @CurrentFilePath varchar(max)
DECLARE @CurrentCleanupTime varchar(max)

DECLARE @CurrentCommand01 varchar(max)
DECLARE @CurrentCommand02 varchar(max)
DECLARE @CurrentCommand03 varchar(max)
DECLARE @CurrentCommand04 varchar(max)

DECLARE @CurrentCommandOutput01 int
DECLARE @CurrentCommandOutput02 int
DECLARE @CurrentCommandOutput03 int
DECLARE @CurrentCommandOutput04 int

DECLARE @DirectoryInfo TABLE ( FileExists bit,
FileIsADirectory bit,
ParentDirectoryExists bit)

DECLARE @tmpDatabases TABLE ( ID int IDENTITY PRIMARY KEY,
DatabaseName varchar(max),
Completed bit)

DECLARE @Error int

SET @Error = 0

----------------------------------------------------------------------------------------------------
--// Log initial information //--
----------------------------------------------------------------------------------------------------

SET @StartMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID)) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Parameters: @Databases = ' + ISNULL('''' + @Databases + '''','NULL')
SET @StartMessage = @StartMessage + ', @Directory = ' + ISNULL('''' + @Directory + '''','NULL')
SET @StartMessage = @StartMessage + ', @BackupType = ' + ISNULL('''' + @BackupType + '''','NULL')
SET @StartMessage = @StartMessage + ', @Verify = ' + ISNULL('''' + @Verify + '''','NULL')
SET @StartMessage = @StartMessage + ', @CleanupTime = ' + ISNULL(CAST(@CleanupTime AS varchar),'NULL')
SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10)

RAISERROR(@StartMessage,10,1) WITH NOWAIT

----------------------------------------------------------------------------------------------------
--// Select databases //--
----------------------------------------------------------------------------------------------------

IF @Databases IS NULL OR @Databases = ''
BEGIN
SET @ErrorMessage = 'The value for parameter @Databases is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

INSERT INTO @tmpDatabases (DatabaseName, Completed)
SELECT DatabaseName AS DatabaseName,
0 AS Completed
FROM dbo.DatabaseSelect (@Databases)
ORDER BY DatabaseName ASC

IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
SET @ErrorMessage = 'Error selecting databases.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

----------------------------------------------------------------------------------------------------
--// Check directory //--
----------------------------------------------------------------------------------------------------

IF NOT (@Directory LIKE '_:' OR @Directory LIKE '_:\%') OR @Directory LIKE '%\' OR @Directory IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @Directory is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

INSERT INTO @DirectoryInfo (FileExists, FileIsADirectory, ParentDirectoryExists)
EXECUTE('EXECUTE xp_FileExist ''' + @Directory + '''')

IF NOT EXISTS (SELECT * FROM @DirectoryInfo WHERE FileExists = 0 AND FileIsADirectory = 1 AND ParentDirectoryExists = 1)
BEGIN
SET @ErrorMessage = 'The directory does not exist.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

----------------------------------------------------------------------------------------------------
--// Check backup type //--
----------------------------------------------------------------------------------------------------

SET @BackupType = UPPER(@BackupType)

IF @BackupType NOT IN ('FULL','DIFF','LOG') OR @BackupType IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @BackupType is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

----------------------------------------------------------------------------------------------------
--// Check Verify input //--
----------------------------------------------------------------------------------------------------

IF @Verify NOT IN ('Y','N') OR @Verify IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @Verify is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

----------------------------------------------------------------------------------------------------
--// Check CleanupTime input //--
----------------------------------------------------------------------------------------------------

IF @CleanupTime < 0 OR @CleanupTime IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @CleanupTime is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

----------------------------------------------------------------------------------------------------
--// Check error variable //--
----------------------------------------------------------------------------------------------------

IF @Error <> 0 GOTO Logging

----------------------------------------------------------------------------------------------------
--// Set global variables //--
----------------------------------------------------------------------------------------------------

SET @InstanceName = REPLACE(CAST(SERVERPROPERTY('servername') AS varchar),'\','$')

SELECT @FileExtension = CASE
WHEN @BackupType = 'FULL' THEN 'bak'
WHEN @BackupType = 'DIFF' THEN 'bak'
WHEN @BackupType = 'LOG' THEN 'trn'
END

----------------------------------------------------------------------------------------------------
--// Execute backup commands //--
----------------------------------------------------------------------------------------------------

WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Completed = 0)
BEGIN

SELECT TOP 1 @CurrentID = ID,
@CurrentDatabase = DatabaseName
FROM @tmpDatabases
WHERE Completed = 0
ORDER BY ID ASC

-- Set database message
SET @DatabaseMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabase) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabase,'status') AS varchar) + CHAR(10)
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT

IF DATABASEPROPERTYEX(@CurrentDatabase,'status') = 'ONLINE'
BEGIN

SET @CurrentDirectory = @Directory + '\' + @InstanceName + '\' + @CurrentDatabase + '\' + @BackupType

SET @CurrentDate = REPLACE(REPLACE(REPLACE((CONVERT(varchar,GETDATE(),120)),'-',''),' ','_'),':','')

SET @CurrentFileName = @InstanceName + '_' + @CurrentDatabase + '_' + @BackupType + '_' + @CurrentDate + '.' + @FileExtension

SET @CurrentFilePath = @CurrentDirectory + '\' + @CurrentFileName

SET @CurrentCleanupTime = CONVERT(varchar(19),(DATEADD(hh,-(@CleanupTime),GETDATE())),126)

-- Create directory
SET @CurrentCommand01 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = master.dbo.xp_create_subdir ''' + @CurrentDirectory + ''' IF @ReturnCode <> 0 RAISERROR(''Error creating directory.'', 16, 1)'
EXECUTE @CurrentCommandOutput01 = [dbo].[CommandExecute] @CurrentCommand01, '', 1
SET @Error = @@ERROR
IF @ERROR <> 0 SET @CurrentCommandOutput01 = @ERROR

-- Perform a backup
IF @CurrentCommandOutput01 = 0
BEGIN
SELECT @CurrentCommand02 = CASE
WHEN @BackupType = 'FULL' THEN 'BACKUP DATABASE ' + QUOTENAME(@CurrentDatabase) + ' TO DISK = ''' + @CurrentFilePath + ''' WITH CHECKSUM'
WHEN @BackupType = 'DIFF' THEN 'BACKUP DATABASE ' + QUOTENAME(@CurrentDatabase) + ' TO DISK = ''' + @CurrentFilePath + ''' WITH CHECKSUM, DIFFERENTIAL'
WHEN @BackupType = 'LOG' THEN 'BACKUP LOG ' + QUOTENAME(@CurrentDatabase) + ' TO DISK = ''' + @CurrentFilePath + ''' WITH CHECKSUM'
END
EXECUTE @CurrentCommandOutput02 = [dbo].[CommandExecute] @CurrentCommand02, '', 1
SET @Error = @@ERROR
IF @ERROR <> 0 SET @CurrentCommandOutput02 = @ERROR
END

-- Verify the backup
IF @CurrentCommandOutput02 = 0 AND @Verify = 'Y'
BEGIN
SET @CurrentCommand03 = 'RESTORE VERIFYONLY FROM DISK = ''' + @CurrentFilePath + ''' WITH CHECKSUM'
EXECUTE @CurrentCommandOutput03 = [dbo].[CommandExecute] @CurrentCommand03, '', 1
SET @Error = @@ERROR
IF @ERROR <> 0 SET @CurrentCommandOutput03 = @ERROR
END

-- Delete old backup files
IF (@CurrentCommandOutput02 = 0 AND @Verify = 'N')
OR (@CurrentCommandOutput02 = 0 AND @Verify = 'Y' AND @CurrentCommandOutput03 = 0)
BEGIN
SET @CurrentCommand04 = 'DECLARE @ReturnCode int EXECUTE @ReturnCode = master.dbo.xp_delete_file 0, ''' + @CurrentDirectory + ''', ''' + @FileExtension + ''', ''' + @CurrentCleanupTime + ''' IF @ReturnCode <> 0 RAISERROR(''Error deleting files.'', 16, 1)'
EXECUTE @CurrentCommandOutput04 = [dbo].[CommandExecute] @CurrentCommand04, '', 1
SET @Error = @@ERROR
IF @ERROR <> 0 SET @CurrentCommandOutput04 = @ERROR
END

END

-- Update that the database is completed
UPDATE @tmpDatabases
SET Completed = 1
WHERE ID = @CurrentID

-- Clear variables
SET @CurrentID = NULL
SET @CurrentDatabase = NULL
SET @CurrentDirectory = NULL
SET @CurrentDate = NULL
SET @CurrentFileName = NULL
SET @CurrentFilePath = NULL
SET @CurrentCleanupTime = NULL

SET @CurrentCommand01 = NULL
SET @CurrentCommand02 = NULL
SET @CurrentCommand03 = NULL
SET @CurrentCommand04 = NULL

SET @CurrentCommandOutput01 = NULL
SET @CurrentCommandOutput02 = NULL
SET @CurrentCommandOutput03 = NULL
SET @CurrentCommandOutput04 = NULL

END

----------------------------------------------------------------------------------------------------
--// Log completing information //--
----------------------------------------------------------------------------------------------------

Logging:

SET @EndMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120)

RAISERROR(@EndMessage,10,1) WITH NOWAIT

----------------------------------------------------------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DatabaseIntegrityCheck]

@Databases varchar(max)

AS

SET NOCOUNT ON

----------------------------------------------------------------------------------------------------
--// Declare variables //--
----------------------------------------------------------------------------------------------------

DECLARE @StartMessage varchar(max)
DECLARE @EndMessage varchar(max)
DECLARE @DatabaseMessage varchar(max)
DECLARE @ErrorMessage varchar(max)

DECLARE @CurrentID int
DECLARE @CurrentDatabase varchar(max)
DECLARE @CurrentCommand01 varchar(max)
DECLARE @CurrentCommandOutput01 int

DECLARE @tmpDatabases TABLE ( ID int IDENTITY PRIMARY KEY,
DatabaseName varchar(max),
Completed bit)

DECLARE @Error int

SET @Error = 0

----------------------------------------------------------------------------------------------------
--// Log initial information //--
----------------------------------------------------------------------------------------------------

SET @StartMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID)) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Parameters: @Databases = ' + ISNULL('''' + @Databases + '''','NULL')
SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10)

RAISERROR(@StartMessage,10,1) WITH NOWAIT

----------------------------------------------------------------------------------------------------
--// Select databases //--
----------------------------------------------------------------------------------------------------

IF @Databases IS NULL OR @Databases = ''
BEGIN
SET @ErrorMessage = 'The value for parameter @Databases is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

INSERT INTO @tmpDatabases (DatabaseName, Completed)
SELECT DatabaseName AS DatabaseName,
0 AS Completed
FROM dbo.DatabaseSelect (@Databases)
ORDER BY DatabaseName ASC

IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
SET @ErrorMessage = 'Error selecting databases.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

----------------------------------------------------------------------------------------------------
--// Check error variable //--
----------------------------------------------------------------------------------------------------

IF @Error <> 0 GOTO Logging

----------------------------------------------------------------------------------------------------
--// Execute commands //--
----------------------------------------------------------------------------------------------------

WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Completed = 0)
BEGIN

SELECT TOP 1 @CurrentID = ID,
@CurrentDatabase = DatabaseName
FROM @tmpDatabases
WHERE Completed = 0
ORDER BY ID ASC

-- Set database message 
SET @DatabaseMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabase) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabase,'status') AS varchar) + CHAR(13) + CHAR(10)
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT

IF DATABASEPROPERTYEX(@CurrentDatabase,'status') = 'ONLINE'
BEGIN
SET @CurrentCommand01 = 'DBCC CHECKDB (' + QUOTENAME(@CurrentDatabase) + ') WITH DATA_PURITY, NO_INFOMSGS'
EXECUTE @CurrentCommandOutput01 = [dbo].[CommandExecute] @CurrentCommand01, '', 1
SET @Error = @@ERROR
IF @ERROR <> 0 SET @CurrentCommandOutput01 = @ERROR
END

-- Update that the database is completed
UPDATE @tmpDatabases
SET Completed = 1
WHERE ID = @CurrentID

-- Clear variables
SET @CurrentID = NULL
SET @CurrentDatabase = NULL
SET @CurrentCommand01 = NULL
SET @CurrentCommandOutput01 = NULL

END

----------------------------------------------------------------------------------------------------
--// Log completing information //--
----------------------------------------------------------------------------------------------------

Logging:

SET @EndMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120)

RAISERROR(@EndMessage,10,1) WITH NOWAIT

----------------------------------------------------------------------------------------------------
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[IndexOptimize]

@Databases varchar(max),
@FragmentationHigh_LOB varchar(max) = 'INDEX_REBUILD_OFFLINE',
@FragmentationHigh_NonLOB varchar(max) = 'INDEX_REBUILD_OFFLINE',
@FragmentationMedium_LOB varchar(max) = 'INDEX_REORGANIZE',
@FragmentationMedium_NonLOB varchar(max) = 'INDEX_REORGANIZE',
@FragmentationLow_LOB varchar(max) = 'NOTHING',
@FragmentationLow_NonLOB varchar(max) = 'NOTHING',
@FragmentationLevel1 tinyint = 5,
@FragmentationLevel2 tinyint = 30,
@PageCountLevel int = 1000

AS

SET NOCOUNT ON

----------------------------------------------------------------------------------------------------
--// Declare variables //--
----------------------------------------------------------------------------------------------------

DECLARE @StartMessage varchar(max)
DECLARE @EndMessage varchar(max)
DECLARE @DatabaseMessage varchar(max)
DECLARE @ErrorMessage varchar(max)

DECLARE @CurrentID int
DECLARE @CurrentDatabase varchar(max)

DECLARE @CurrentCommandSelect01 varchar(max)
DECLARE @CurrentCommandSelect02 varchar(max)
DECLARE @CurrentCommandSelect03 varchar(max)

DECLARE @CurrentCommand01 varchar(max)
DECLARE @CurrentCommand02 varchar(max)

DECLARE @CurrentCommandOutput01 int
DECLARE @CurrentCommandOutput02 int

DECLARE @CurrentIxID int
DECLARE @CurrentSchemaID int
DECLARE @CurrentSchemaName varchar(max)
DECLARE @CurrentObjectID int
DECLARE @CurrentObjectName varchar(max)
DECLARE @CurrentIndexID int
DECLARE @CurrentIndexName varchar(max)
DECLARE @CurrentIndexType int
DECLARE @CurrentIndexExists bit
DECLARE @CurrentIsLOB bit
DECLARE @CurrentFragmentationLevel float
DECLARE @CurrentPageCount bigint
DECLARE @CurrentAction varchar(max)
DECLARE @CurrentComment varchar(max)

DECLARE @tmpDatabases TABLE ( ID int IDENTITY PRIMARY KEY,
DatabaseName varchar(max),
Completed bit)

DECLARE @tmpIndexes TABLE ( IxID int IDENTITY PRIMARY KEY,
SchemaID int,
SchemaName varchar(max),
ObjectID int,
ObjectName varchar(max),
IndexID int,
IndexName varchar(max),
IndexType int,
Completed bit)

DECLARE @tmpIndexExists TABLE ([Count] int)

DECLARE @tmpIsLOB TABLE ([Count] int)

DECLARE @Actions TABLE ([Action] varchar(max))

INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_ONLINE')
INSERT INTO @Actions([Action]) VALUES('INDEX_REBUILD_OFFLINE')
INSERT INTO @Actions([Action]) VALUES('INDEX_REORGANIZE')
INSERT INTO @Actions([Action]) VALUES('STATISTICS_UPDATE')
INSERT INTO @Actions([Action]) VALUES('INDEX_REORGANIZE_STATISTICS_UPDATE')
INSERT INTO @Actions([Action]) VALUES('NOTHING')

DECLARE @Error int

SET @Error = 0

----------------------------------------------------------------------------------------------------
--// Log initial information //--
----------------------------------------------------------------------------------------------------

SET @StartMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Procedure: ' + QUOTENAME(DB_NAME(DB_ID())) + '.' + QUOTENAME(OBJECT_SCHEMA_NAME(@@PROCID)) + '.' + QUOTENAME(OBJECT_NAME(@@PROCID)) + CHAR(13) + CHAR(10)
SET @StartMessage = @StartMessage + 'Parameters: @Databases = ' + ISNULL('''' + @Databases + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationHigh_LOB = ' + ISNULL('''' + @FragmentationHigh_LOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationHigh_NonLOB = ' + ISNULL('''' + @FragmentationHigh_NonLOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationMedium_LOB = ' + ISNULL('''' + @FragmentationMedium_LOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationMedium_NonLOB = ' + ISNULL('''' + @FragmentationMedium_NonLOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLow_LOB = ' + ISNULL('''' + @FragmentationLow_LOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLow_NonLOB = ' + ISNULL('''' + @FragmentationLow_NonLOB + '''','NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLevel1 = ' + ISNULL(CAST(@FragmentationLevel1 AS varchar),'NULL')
SET @StartMessage = @StartMessage + ', @FragmentationLevel2 = ' + ISNULL(CAST(@FragmentationLevel2 AS varchar),'NULL')
SET @StartMessage = @StartMessage + ', @PageCountLevel = ' + ISNULL(CAST(@PageCountLevel AS varchar),'NULL')
SET @StartMessage = @StartMessage + CHAR(13) + CHAR(10)

RAISERROR(@StartMessage,10,1) WITH NOWAIT

----------------------------------------------------------------------------------------------------
--// Select databases //--
----------------------------------------------------------------------------------------------------

IF @Databases IS NULL OR @Databases = ''
BEGIN
SET @ErrorMessage = 'The value for parameter @Databases is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

INSERT INTO @tmpDatabases (DatabaseName, Completed)
SELECT DatabaseName AS DatabaseName,
0 AS Completed
FROM dbo.DatabaseSelect (@Databases)
ORDER BY DatabaseName ASC

IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
SET @ErrorMessage = 'Error selecting databases.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

----------------------------------------------------------------------------------------------------
--// Check input parameters //--
----------------------------------------------------------------------------------------------------

IF @FragmentationHigh_LOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE')
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationHigh_LOB is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

IF @FragmentationHigh_NonLOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE' OR SERVERPROPERTY('EngineEdition') = 3)
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationHigh_NonLOB is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

IF @FragmentationMedium_LOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE')
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationMedium_LOB is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

IF @FragmentationMedium_NonLOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE' OR SERVERPROPERTY('EngineEdition') = 3)
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationMedium_NonLOB is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

IF @FragmentationLow_LOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE')
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationLow_LOB is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

IF @FragmentationLow_NonLOB NOT IN(SELECT [Action] FROM @Actions WHERE [Action] <> 'INDEX_REBUILD_ONLINE' OR SERVERPROPERTY('EngineEdition') = 3)
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationLow_NonLOB is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

IF @FragmentationLevel1 <= 0 OR @FragmentationLevel1 >= 100 OR @FragmentationLevel1 >= @FragmentationLevel2 OR @FragmentationLevel1 IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationLevel1 is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

IF @FragmentationLevel2 <= 0 OR @FragmentationLevel2 >= 100 OR @FragmentationLevel2 <= @FragmentationLevel1 OR @FragmentationLevel2 IS NULL 
BEGIN
SET @ErrorMessage = 'The value for parameter @FragmentationLevel2 is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

IF @PageCountLevel < 0 OR @PageCountLevel IS NULL
BEGIN
SET @ErrorMessage = 'The value for parameter @PageCountLevel is not supported.' + CHAR(13) + CHAR(10)
RAISERROR(@ErrorMessage,16,1) WITH NOWAIT
SET @Error = @@ERROR
END

----------------------------------------------------------------------------------------------------
--// Check error variable //--
----------------------------------------------------------------------------------------------------

IF @Error <> 0 GOTO Logging

----------------------------------------------------------------------------------------------------
--// Execute commands //--
----------------------------------------------------------------------------------------------------

WHILE EXISTS (SELECT * FROM @tmpDatabases WHERE Completed = 0)
BEGIN

SELECT TOP 1 @CurrentID = ID,
@CurrentDatabase = DatabaseName
FROM @tmpDatabases
WHERE Completed = 0
ORDER BY ID ASC

-- Set database message
SET @DatabaseMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Database: ' + QUOTENAME(@CurrentDatabase) + CHAR(13) + CHAR(10)
SET @DatabaseMessage = @DatabaseMessage + 'Status: ' + CAST(DATABASEPROPERTYEX(@CurrentDatabase,'status') AS varchar) + CHAR(13) + CHAR(10)
RAISERROR(@DatabaseMessage,10,1) WITH NOWAIT

IF DATABASEPROPERTYEX(@CurrentDatabase,'status') = 'ONLINE'
BEGIN

-- Select indexes in the current database
SET @CurrentCommandSelect01 = 'SELECT sys.schemas.[schema_id], sys.schemas.[name], sys.objects.[object_id], sys.objects.[name], sys.indexes.index_id, sys.indexes.[name], sys.indexes.[type], 0 FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.objects ON sys.indexes.[object_id] = sys.objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas ON sys.objects.[schema_id] = sys.schemas.[schema_id] WHERE sys.objects.type = ''U'' AND sys.objects.is_ms_shipped = 0 AND sys.indexes.[type] IN(1,2) ORDER BY sys.schemas.[schema_id] ASC, sys.objects.[object_id] ASC, sys.indexes.index_id ASC'

INSERT INTO @tmpIndexes (SchemaID, SchemaName, ObjectID, ObjectName, IndexID, IndexName, IndexType, Completed)
EXECUTE(@CurrentCommandSelect01)

WHILE EXISTS (SELECT * FROM @tmpIndexes WHERE Completed = 0)
BEGIN

SELECT TOP 1 @CurrentIxID = IxID,
@CurrentSchemaID = SchemaID,
@CurrentSchemaName = SchemaName,
@CurrentObjectID = ObjectID,
@CurrentObjectName = ObjectName,
@CurrentIndexID = IndexID,
@CurrentIndexName = IndexName,
@CurrentIndexType = IndexType
FROM @tmpIndexes
WHERE Completed = 0
ORDER BY IxID ASC

-- Does the index exist?
SET @CurrentCommandSelect02 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.indexes INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.objects ON sys.indexes.[object_id] = sys.objects.[object_id] INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.schemas ON sys.objects.[schema_id] = sys.schemas.[schema_id] WHERE sys.objects.type = ''U'' AND sys.indexes.index_id > 0 AND sys.schemas.[schema_id] = ' + CAST(@CurrentSchemaID AS varchar) + ' AND sys.schemas.[name] = ''' + @CurrentSchemaName + ''' AND sys.objects.[object_id] = ' + CAST(@CurrentObjectID AS varchar) + ' AND sys.objects.[name] = ''' + @CurrentObjectName + ''' AND sys.indexes.index_id = ' + CAST(@CurrentIndexID AS varchar) + ' AND sys.indexes.[name] = ''' + @CurrentIndexName + ''' AND sys.indexes.[type] = ' + CAST(@CurrentIndexType AS varchar)

INSERT INTO @tmpIndexExists ([Count])
EXECUTE(@CurrentCommandSelect02)

IF (SELECT [Count] FROM @tmpIndexExists) > 0 BEGIN SET @CurrentIndexExists = 1 END ELSE BEGIN SET @CurrentIndexExists = 0 END

IF @CurrentIndexExists = 0 GOTO NoAction

-- Does the index contain a LOB?
IF @CurrentIndexType = 1 SET @CurrentCommandSelect03 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.columns INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.types ON sys.columns.system_type_id = sys.types.user_type_id WHERE sys.columns.[object_id] = ' + CAST(@CurrentObjectID AS varchar) + ' AND (sys.types.name IN(''xml'',''image'',''text'',''ntext'') OR (sys.types.name IN(''varchar'',''nvarchar'',''varbinary'',''nvarbinary'') AND sys.columns.max_length = -1))' 
IF @CurrentIndexType = 2 SET @CurrentCommandSelect03 = 'SELECT COUNT(*) FROM ' + QUOTENAME(@CurrentDatabase) + '.sys.index_columns INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.columns ON sys.index_columns.[object_id] = sys.columns.[object_id] AND sys.index_columns.column_id = sys.columns.column_id INNER JOIN ' + QUOTENAME(@CurrentDatabase) + '.sys.types ON sys.columns.system_type_id = sys.types.user_type_id WHERE sys.index_columns.[object_id] = ' + CAST(@CurrentObjectID AS varchar) + ' AND sys.index_columns.index_id = ' + CAST(@CurrentIndexID AS varchar) + ' AND (sys.types.[name] IN(''xml'',''image'',''text'',''ntext'') OR (sys.types.[name] IN(''varchar'',''nvarchar'',''varbinary'',''nvarbinary'') AND sys.types.max_length = -1))'

INSERT INTO @tmpIsLOB ([Count])
EXECUTE(@CurrentCommandSelect03)

IF (SELECT [Count] FROM @tmpIsLOB) > 0 BEGIN SET @CurrentIsLOB = 1 END ELSE BEGIN SET @CurrentIsLOB = 0 END

-- Is the index fragmented?
SELECT @CurrentFragmentationLevel = avg_fragmentation_in_percent,
@CurrentPageCount = page_count
FROM sys.dm_db_index_physical_stats(DB_ID(@CurrentDatabase), @CurrentObjectID, @CurrentIndexID, NULL, 'LIMITED')
WHERE alloc_unit_type_desc = 'IN_ROW_DATA'
AND index_level = 0

-- Decide action
SELECT @CurrentAction = CASE
WHEN @CurrentIsLOB = 1 AND @CurrentFragmentationLevel >= @FragmentationLevel2 AND @CurrentPageCount >= @PageCountLevel THEN @FragmentationHigh_LOB
WHEN @CurrentIsLOB = 0 AND @CurrentFragmentationLevel >= @FragmentationLevel2 AND @CurrentPageCount >= @PageCountLevel THEN @FragmentationHigh_NonLOB
WHEN @CurrentIsLOB = 1 AND @CurrentFragmentationLevel >= @FragmentationLevel1 AND @CurrentFragmentationLevel < @FragmentationLevel2 AND @CurrentPageCount >= @PageCountLevel THEN @FragmentationMedium_LOB
WHEN @CurrentIsLOB = 0 AND @CurrentFragmentationLevel >= @FragmentationLevel1 AND @CurrentFragmentationLevel < @FragmentationLevel2 AND @CurrentPageCount >= @PageCountLevel THEN @FragmentationMedium_NonLOB
WHEN @CurrentIsLOB = 1 AND (@CurrentFragmentationLevel < @FragmentationLevel1 OR @CurrentPageCount < @PageCountLevel) THEN @FragmentationLow_LOB
WHEN @CurrentIsLOB = 0 AND (@CurrentFragmentationLevel < @FragmentationLevel1 OR @CurrentPageCount < @PageCountLevel) THEN @FragmentationLow_NonLOB
END

-- Create comment
SET @CurrentComment = 'IndexType: ' + CAST(@CurrentIndexType AS varchar) + ', '
SET @CurrentComment = @CurrentComment + 'LOB: ' + CAST(@CurrentIsLOB AS varchar) + ', '
SET @CurrentComment = @CurrentComment + 'PageCount: ' + CAST(@CurrentPageCount AS varchar) + ', '
SET @CurrentComment = @CurrentComment + 'Fragmentation: ' + CAST(@CurrentFragmentationLevel AS varchar)

IF @CurrentAction IN('INDEX_REBUILD_ONLINE','INDEX_REBUILD_OFFLINE','INDEX_REORGANIZE','INDEX_REORGANIZE_STATISTICS_UPDATE')
BEGIN
SELECT @CurrentCommand01 = CASE
WHEN @CurrentAction = 'INDEX_REBUILD_ONLINE' THEN 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentDatabase) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = ON)'
WHEN @CurrentAction = 'INDEX_REBUILD_OFFLINE' THEN 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentDatabase) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' REBUILD WITH (SORT_IN_TEMPDB = ON, ONLINE = OFF)'
WHEN @CurrentAction IN('INDEX_REORGANIZE','INDEX_REORGANIZE_STATISTICS_UPDATE') THEN 'ALTER INDEX ' + QUOTENAME(@CurrentIndexName) + ' ON ' + QUOTENAME(@CurrentDatabase) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' REORGANIZE'
END
EXECUTE @CurrentCommandOutput01 = [dbo].[CommandExecute] @CurrentCommand01, @CurrentComment, 2
SET @Error = @@ERROR
IF @ERROR <> 0 SET @CurrentCommandOutput01 = @ERROR
END

IF @CurrentAction IN('INDEX_REORGANIZE_STATISTICS_UPDATE','STATISTICS_UPDATE')
BEGIN
SET @CurrentCommand02 = 'UPDATE STATISTICS ' + QUOTENAME(@CurrentDatabase) + '.' + QUOTENAME(@CurrentSchemaName) + '.' + QUOTENAME(@CurrentObjectName) + ' ' + QUOTENAME(@CurrentIndexName)
EXECUTE @CurrentCommandOutput02 = [dbo].[CommandExecute] @CurrentCommand02, @CurrentComment, 2
SET @Error = @@ERROR
IF @ERROR <> 0 SET @CurrentCommandOutput02 = @ERROR
END

NoAction:

-- Update that the index is completed
UPDATE @tmpIndexes
SET Completed = 1
WHERE IxID = @CurrentIxID

-- Clear variables
SET @CurrentCommandSelect02 = NULL
SET @CurrentCommandSelect03 = NULL

SET @CurrentCommand01 = NULL
SET @CurrentCommand02 = NULL

SET @CurrentCommandOutput01 = NULL
SET @CurrentCommandOutput02 = NULL

SET @CurrentIxID = NULL
SET @CurrentSchemaID = NULL
SET @CurrentSchemaName = NULL
SET @CurrentObjectID = NULL
SET @CurrentObjectName = NULL
SET @CurrentIndexID = NULL
SET @CurrentIndexName = NULL
SET @CurrentIndexType = NULL
SET @CurrentIndexExists = NULL
SET @CurrentIsLOB = NULL
SET @CurrentFragmentationLevel = NULL
SET @CurrentPageCount = NULL
SET @CurrentAction = NULL
SET @CurrentComment = NULL

DELETE FROM @tmpIndexExists
DELETE FROM @tmpIsLOB

END

END

-- Update that the database is completed
UPDATE @tmpDatabases
SET Completed = 1
WHERE ID = @CurrentID

-- Clear variables
SET @CurrentID = NULL
SET @CurrentDatabase = NULL

SET @CurrentCommandSelect01 = NULL

DELETE FROM @tmpIndexes

END

----------------------------------------------------------------------------------------------------
--// Log completing information //--
----------------------------------------------------------------------------------------------------

Logging:

SET @EndMessage = 'DateTime: ' + CONVERT(varchar,GETDATE(),120)

RAISERROR(@EndMessage,10,1) WITH NOWAIT

----------------------------------------------------------------------------------------------------
GO

SQL Server Central: usp_IndexesUnused - SQL Server 2005

usp_IndexesUnused - SQL 2k5 - SQL Server Central
checks for unused indexes (no updates or user hits). DB name is authorative.
Usage: EXEC usp_IndexesUnused

IF EXISTS (SELECT name FROM dbo.sysobjects WHERE id = Object_id(N'[dbo].[usp_Indexesunused]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[usp_IndexesUnused]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROC usp_IndexesUnused @DBName VARCHAR(255) = NULL
AS
--
--  usp_IndexesUnused.sql - Checks for unused indexes (no updates or user hits)
--  
--  2008-03-09 Pedro Lopes (NovaBase) pedro.lopes@novabase.pt
--
--  EXEC usp_IndexesUnused 
--
SET NOCOUNT ON
IF @DBName IS NULL
BEGIN
SELECT 'DB name is authorative.' AS 'WARNING - SYNTAX ERROR!'
RETURN
END
DECLARE @DBID int
SELECT @DBID = DB_ID(@DBName)
DECLARE @SQLcmd NVARCHAR(max)
SET @SQLcmd = 'USE [' + @DBName + '];
SELECT ''[' + @DBName + ']'' AS DBName,
OBJECT_NAME(a.object_id) AS ''Table'',
c.name AS ''IndexName'',
(SELECT used/128 FROM sysindexes b WHERE b.id = a.object_id AND b.name=c.name AND c.index_id = b.indid) AS ''Size_MB'',
(a.user_seeks + a.user_scans + a.user_lookups) AS ''Hits'',
RTRIM(CONVERT(NVARCHAR(10),CAST(CASE WHEN (a.user_seeks + a.user_scans + a.user_lookups) = 0 THEN 0 ELSE CONVERT(REAL, (a.user_seeks + a.user_scans + a.user_lookups)) * 100 / 
CASE (a.user_seeks + a.user_scans + a.user_lookups + a.user_updates) WHEN 0 THEN 1 ELSE CONVERT(REAL, (a.user_seeks + a.user_scans + a.user_lookups + a.user_updates)) END END AS DECIMAL(18,2)))) + ''/'' +
RTRIM(CONVERT(NVARCHAR(10),CAST(CASE WHEN a.user_updates = 0 THEN 0 ELSE CONVERT(REAL, a.user_updates) * 100 / 
CASE (a.user_seeks + a.user_scans + a.user_lookups + a.user_updates) WHEN 0 THEN 1 ELSE CONVERT(REAL, (a.user_seeks + a.user_scans + a.user_lookups + a.user_updates)) END END AS DECIMAL(18,2)))) AS [R/W_Ratio],
a.user_updates AS ''Updates'', --indicates the level of maintenance on the index caused by insert, update, or delete operations on the underlying table or view
a.last_user_update AS ''Update_Date''
FROM sys.dm_db_index_usage_stats a
JOIN sysobjects AS o ON (a.OBJECT_ID = o.id)
JOIN sys.indexes AS c ON (a.OBJECT_ID = c.OBJECT_ID AND a.index_id = c.index_id)
WHERE o.type = ''U''   -- exclude system tables
AND c.is_unique = 0   -- no unique indexes
AND c.type = 2    -- nonclustered indexes only
AND c.is_primary_key = 0  -- no primary keys
AND c.is_unique_constraint = 0  -- no unique constraints
AND c.is_disabled = 0   -- only active indexes
AND a.database_id = ' + CAST(@DBID AS CHAR(4)) + ' -- for current database only
AND ((a.user_seeks + a.user_scans + a.user_lookups) = 0 OR a.user_updates = 0)
ORDER BY OBJECT_NAME(a.object_id), a.user_updates'
EXEC master..sp_executesql @SQLcmd
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SQL Server Central: SQL 2005 IndexDefrag solution

SQL 2005 IndexDefrag solution - SQL Server Central

set @strIndex=(select IndexName from ##ADMIN where ID2=@i)
set @strSql=(@strpre+@strTable+@strmid+@strIndex+@strEnd)

--set @strSql=@strSql+ @strIndex+') ' 
--print(@strIndex) print @i

--print @strSql
if 2<= datepart(hh,convert(datetime,getdate(),121))
and 8>datepart(hh,convert(datetime,getdate(),121))
begin
exec (@strSql);
if @@error<>0
begin
set @err=0
end
print (@i)print (@err)Print(@start)print(@strTable)
insert into aa.dbo.ZZDataBaseAdminLog (ID2,tablename,ilog) values (@i,@strTable+' '+@strIndex, @err)


end
set @i=@i+1
end 


end







/*

drop table ##Admin
go
SELECT OBJECT_NAME(i.object_id) AS TableName,
i.name AS IndexName,indexstats.*,
indexstats.avg_fragmentation_in_percent as FRAG
into ##Admin
FROM sys.dm_db_index_physical_stats(9, NULL, NULL, NULL, 'Limited') indexstats
INNER JOIN sys.indexes i ON i.object_id = indexstats.object_id
AND i.index_id = indexstats.index_id


where i.name is not null
order by indexstats.avg_fragmentation_in_percent desc

select * from ##admin

select top 10 * from sys.indexes
*/