Searching for table inforation in SQL jobs

I'm putting this here to make it easier to look at job and job step information quickly.


USE [msdb]
GO
/****** Object: StoredProcedure [dbo].[JobInformation_SqlSSISJobs_SelectBySSISFileName]
Script Date: 06/24/2009 13:32:21 ******

This script is used to return every job step where a string occurs

*/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE procedure [dbo].[JobInformation_SqlSSISJobs_SelectBySSISFileName]
@fileName as varchar(125)
AS

/*
declare @fileName as varchar(125)
set @fileName = 'Order Invoice Import Data.dstConfig'
*/

select
[name] as JobName
, step.step_id as JobStepNbr
, step.step_name as JobStepName
, subsystem as JobStepType
, command as JobStepCommand
, database_name AS JobStepDbName
, last_run_date AS JobStepLastRunDate
, last_run_time AS JobStepLastRunTime
, next_run_date AS JobStepNextRunDate
, next_run_time AS JobStepNextRunTime
, run_status AS JobStepLastRunStatus
--, hist.*
FROM msdb.dbo.sysjobs job
INNER JOIN msdb.dbo.sysjobsteps step
ON job.job_id = step.job_id
INNER JOIN msdb.dbo.sysjobschedules sch
ON job.job_id = sch.job_id
LEFT OUTER JOIN dbo.sysjobhistory hist
ON job.job_id = hist.job_id
AND step.step_id = hist.step_id
AND step.last_run_date = hist.run_date
AND step.last_run_time = hist.run_time
WHERE command like '%' + @fileName + '%';



----------------
Now playing: NPR - 0906171: NPR: 06-20-2009 Wait Wait... Don't Tell Me!
via FoxyTunes

SQL Check DB

/*
This is the routine I use for running check DB. It simply loops through the master miles and runs check DB. There is an optional parameter to suppress info messages.
*/

USE [master]
GO

DECLARE @tDatabases TABLE
( databaseId int
);

INSERT INTO @tDatabases ( databaseId )
SELECT DISTINCT s_mf.database_id
FROM sys.databases s_mf
WHERE s_mf.state = 0 -- ONLINE
AND has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
AND NOT [NAME] IN ('tempdb', 'master', 'model', 'msdb' )--eliminate temp db
;

DECLARE cursor_usage CURSOR FOR SELECT databaseId FROM @tDatabases;

DECLARE @dbId INT;

OPEN cursor_usage;
FETCH NEXT FROM cursor_usage INTO @dbId;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT db_name(@dbId);
DECLARE @dbname nvarchar(50)
SET @dbname = db_name(@dbId);
DBCC CHECKDB (@dbName);

FETCH NEXT FROM cursor_usage INTO @dbId;
END;

CLOSE cursor_usage;
DEALLOCATE cursor_usage;

SQL User Backup Scipt Update and Fixed

/*
Fixed Backup script
The root path was not set causing the directories to not be set correctly.

Again the master.dbo.xp_create_subdir is only available in 2005 and 2008

*/

USE [master]
GO

DECLARE @rootpath NVARCHAR(100);
set @rootPath = N'\[Your backup locationand a]\';

DECLARE @tDatabases TABLE
( databaseId int
);

INSERT INTO @tDatabases ( databaseId )
SELECT DISTINCT
s_mf.database_id
from sys.master_files s_mf
where s_mf.state = 0 -- ONLINE
AND has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
AND NOT db_name(s_mf.database_id) in ('tempdb', 'master', 'msdb', 'model'); --eliminate temp db


DECLARE cursor_usage CURSOR FOR SELECT databaseId FROM @tDatabases;

DECLARE @dbId int;
OPEN cursor_usage;

FETCH NEXT FROM cursor_usage INTO @dbId;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT db_name(@dbId);
DECLARE @Filepath NVARCHAR(500);
declare @dbname nvarchar(50)
set @dbname = db_name(@dbId);

SET @FilePath = @RootPath + @dbName;
PRINT @FilePath

EXECUTE master.dbo.xp_create_subdir @filepath;

declare @fileName nvarchar(128)
declare @fullPath nvarchar(255)

--set the date format yyyymmddhhmm for the file name
declare @date nvarchar(10)
declare @hour nvarchar(4)
declare @minute nvarchar(4)

--ensure hour is 2 digit
set @hour = CAST(datepart(hh,getdate()) AS nvarchar(2))
if len(@hour) = 1
set @hour = '0' + CAST(datepart(hh,getdate()) AS nvarchar(2))

--ensure hour is 2 digit
set @minute = CAST(DATEPART(mi, GetDate())AS nvarchar(2))

if len(@minute) = 1
set @minute = '0' + CAST(DATEPART(mi, GetDate())AS nvarchar(2))

--set the filename
select @filename = @dbName + N'_backup_' + CONVERT( nvarchar(30), GetDate(), 112 ) + @hour + @minute + N'.bak';
select @fullPath = @filePath + '\' + @fileName
print @fileName;

BACKUP DATABASE @dbname TO DISK = @fullpath WITH NOFORMAT, NOINIT, NAME = @fileName, SKIP, REWIND, NOUNLOAD, STATS = 5;


--run the verify
declare @backupSetId as int
select @backupSetId = position from msdb.dbo.backupset where database_name=@dbname and backup_set_id=(select max(backup_set_id) from msdb.dbo.backupset where database_name=@dbname )

if @backupSetId is null
begin
declare @errMsg nvarchar(128)
select @errMsg = N'Verify failed. Backup information for database ' + @dbname + N' not found.'
raiserror(@errMsg, 16, 1);
end

RESTORE VERIFYONLY FROM DISK = @fullpath WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

FETCH NEXT FROM cursor_usage INTO @dbId;
END;

CLOSE cursor_usage;
DEALLOCATE cursor_usage;

SSIS Transfer Database task

Just a few notes about the use of the transfer database control task in SSIS 2005.

We had several problems with the logins being corrupted when the database was placed in the new instances. It appears that the task attempts to match the users to the instances users as one of its' steps. If it cannot find the user names in the new instance it corrupts the dbo users and schema.

The simple solution is to make sure that all users exist in the new instance. The transfer database task will then match the DBID of the instance users. This saves you from having to run sp_change_users_login to match the DBID from the new database to the instance

Thanks you Sam for this one.

Improved SQL Index Update Script

I have modified this script to be compatible with the Share Point server database I am now supporting. This script replaces the previous one completely. This is used in conjunction with a script that feeds the database information for every database to it. I normally place it in master so I do not have to add it to every database.

USE [master]
GO/****** Object: StoredProcedure [dbo].[DbUtil_ALTER_INDEX] Script Date: 06/05/2008 16:18:35 ******/
SET ANSI_NULLS ON
GOSET QUOTED_IDENTIFIER ON
GO-------------------------------------------------------------------------------------------
-- OBJECT NAME : DbUtil_ALTER_INDEX

-- AUTHOR: Alan T
-- BASED ON A SCRIPT: Tara Kizer

---- DATE : February 27, 2007
--:2008 - 06- 06 Updated to run online and offline
--:2008-11-12 Added Share Point support
--:2009-01-13 Added a trimmer to make sure the database name is good

-- INPUTS : @dbName - name of the database
-- @statsMode - mode to use for sys.dm_db_index_physical_stats
-- @defragType - REORGANIZE (INDEXDEFRAG) or REBUILD (DBREINDEX)
-- @minFragPercent - minimum fragmentation level
-- @maxFragPercent - maximum fragmentation level
-- @minRowCount - minimum row count
-- @PerformOnlineOnly - Forces to rebuild to be online for enterprise ed. without 1 = true
--
-- OUTPUTS : None
--
-- DEPENDENCIES : sys.dm_db_index_physical_stats, sys.objects,
-- sys.schemas, sys.indexes, sys.partitions
--
-- DESCRIPTION : Defragments indexes
/*
EXEC DbUtil_ALTER_INDEX
@dbName = 'DatabaseName',
@statsMode = 'SAMPLED',
@defragType = 'REBUILD',
@minFragPercent = 10,
@maxFragPercent = 100,
@minRowCount = 1000
, @PerformOnlineOnly = 1
*/
-------------------------------------------------------------------------------------------
CREATE PROC [dbo].[DbUtil_ALTER_INDEX]
(
@dbName sysname
, @statsMode varchar(8) = 'SAMPLED'
, @defragType varchar(10) = 'REORGANIZE'
, @minFragPercent int = 25
, @maxFragPercent int = 100
, @minRowCount int = 0
, @PerformOnlineOnly bit = 1
)
AS
SET NOCOUNT ON
DECLARE @i int
DECLARE @objectId int
DECLARE @objectName sysname
DECLARE @indexId int
DECLARE @indexName sysname
DECLARE @schemaName sysname
DECLARE @partitionNumber int
DECLARE @partitionCount int
DECLARE @recordCount bigint
DECLARE @sql nvarchar(4000)
DECLARE @edition int
DECLARE @parmDef nvarchar(500)
SELECT @i = 1, @edition = CONVERT(int, SERVERPROPERTY('EngineEdition'))
--Make sure the temp table does not exist
IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type = N'U' AND name like N'#FragIndex%') DROP TABLE #FragIndex;
SELECT
IDENTITY(int, 1, 1) AS FragIndexId,
[object_id] AS ObjectId,
index_id AS IndexId,
avg_fragmentation_in_percent AS FragPercent,
record_count AS RecordCount,
partition_number AS PartitionNumberINTO #FragIndexFROM sys.dm_db_index_physical_stats (DB_ID(@dbName), NULL, NULL, NULL, @statsMode)WHERE avg_fragmentation_in_percent > @minFragPercentAND avg_fragmentation_in_percent < @maxFragPercent AND index_id > 0AND record_count > @minRowCountORDER BY ObjectId
SELECT
@objectId = ObjectId,
@indexId = IndexId,
@recordCount = RecordCountFROM #FragIndexWHERE FragIndexId = @i
--Make sure that the DB name is trimmed otherwise the procedure will failSELECT @dbName = LTRIM(RTRIM(@dbName))

WHILE @@ROWCOUNT <> 0
BEGIN
SET @sql = '
SELECT @objectName = o.[name], @schemaName = s.[name]
FROM [' + @dbName + '].sys.objects o
JOIN [' + @dbName + '].sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.[object_id] = @objectId'
SET @parmDef = N'@objectId int, @objectName sysname OUTPUT, @schemaName sysname OUTPUT'

EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId,
@objectName = @objectName OUTPUT, @schemaName = @schemaName OUTPUT

SET @sql = 'SELECT @indexName = [name]
FROM [' + @dbName + '].sys.indexes
WHERE [object_id] = @objectId AND index_id = @indexId AND is_disabled = 0'

SET @parmDef = N'@objectId int, @indexId int, @indexName sysname OUTPUT'
EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId, @indexId = @indexId,
@indexName = @indexName OUTPUT

SET @sql = 'SELECT @partitionCount = COUNT(*)
FROM [' + @dbName + '].sys.partitions WHERE [object_id] = @objectId AND index_id = @indexId'
SET @parmDef = N'@objectId int, @indexId int, @partitionCount int OUTPUT'
EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId, @indexId = @indexId,
@partitionCount = @partitionCount OUTPUT
SET @sql = 'ALTER INDEX [' + @indexName + '] ON [' + @dbName + '].[' +
@schemaName + '].[' + @objectName + '] ' + @defragType
-- Developer and Enterprise have the ONLINE = ON option for REBUILD
IF @defragType = 'REBUILD' AND @edition = 3 AND @PerformOnlineOnly = 1 SET @sql = @sql + ' WITH (ONLINE = ON)'
ELSE IF @defragType = 'REBUILD' AND @edition = 3 AND @PerformOnlineOnly = 0 SET @sql = @sql + ' WITH (ONLINE = OFF)'
IF @partitionCount > 1 SET @sql = @sql + ' PARTITION = ' + CAST(@partitionNumber AS varchar(10))
print @sql
BEGIN TRY
EXEC (@SQL)
END TRY
BEGIN CATCH
print 'ERROR1 : ' + cast(ERROR_LINE() as nvarchar(10)); print 'ERROR2 : ' + ERROR_MESSAGE();

RETURN -1;END CATCH;


SET @i = @i + 1
SELECT
@objectId = ObjectId,
@indexId = IndexId,
@recordCount = RecordCountFROM #FragIndexWHERE FragIndexId = @iEND

IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type = N'U' AND name like N'#FragIndex%') DROP TABLE #FragIndex;

return 0;

New features in SQL Server 2008

Direct from Microsoft: Microsoft Technet Page

The ones that will affect me soonest are:
1. Filestream data type to allow the delivey of documents directly to the .NET apps
2. The date data type. The old datetime was a dinosaur and needed to go. It will be replaced by datetime2.

Enjoy

All Database full backup

/*
This script is to loop through all non-temp databases and create a full backup in the
designated directory. It also verifies that the backup when complete. Each database
will be place in their own directory.

The master.dbo.xp_create_subdir only works with SQL Server 2005. It can be removed for 2000 if the directory already exists.
*/

USE [master]

GO

set @filePath = N'\\Server Backup Location\' + @dbName;

DECLARE @tDatabases TABLE

( databaseId int

--, DATABASE_NAME varchar(25)

);

INSERT INTO @tDatabases ( databaseId )

SELECT DISTINCT s_mf.database_id

from sys.master_files s_mf

where s_mf.state = 0 -- ONLINE

AND has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access

AND NOT db_name(s_mf.database_id) in ('tempdb'); --eliminate temp db

DECLARE cursor_usage CURSOR FOR

SELECT databaseId FROM @tDatabases;

DECLARE @dbId int;

OPEN cursor_usage;

FETCH NEXT FROM cursor_usage INTO @dbId;

WHILE @@FETCH_STATUS = 0

BEGIN

PRINT db_name(@dbId);

declare @dbname nvarchar(50)

declare @filepath nvarchar(50)

set @dbname = db_name(@dbId);

EXECUTE master.dbo.xp_create_subdir @filepath;

declare @fileName nvarchar(128)

declare @fullPath nvarchar(255)

--set the date format yyyymmddhhmm for the file name

declare @date nvarchar(10)

declare @hour nvarchar(4)

declare @minute nvarchar(4)

--ensure hour is 2 digit

set @hour = CAST(datepart(hh,getdate()) AS nvarchar(2))

if len(@hour) = 1

set @hour = '0' + CAST(datepart(hh,getdate()) AS nvarchar(2))

--ensure hour is 2 digit

set @minute = CAST(DATEPART(mi, GetDate())AS nvarchar(2))

if len(@minute) = 1

set @minute = '0' + CAST(DATEPART(mi, GetDate())AS nvarchar(2))

--set the filename

select @filename = @dbName + N'_backup_' + CONVERT( nvarchar(30), GetDate(), 112 ) + @hour + @minute + N'.bak';

select @fullPath = @filePath + '\' + @fileName

print @fileName;

BACKUP DATABASE @dbname TO DISK = @fullpath WITH NOFORMAT, NOINIT, NAME = @fileName, SKIP, REWIND, NOUNLOAD, STATS = 5;

--run the verify

declare @backupSetId as int

select @backupSetId = position from msdb.dbo.backupset where database_name=@dbname and backup_set_id=(select max(backup_set_id) from msdb.dbo.backupset where database_name=@dbname )

if @backupSetId is null

begin

declare @errMsg nvarchar(128)

select @errMsg = N'Verify failed. Backup information for database ' + @dbname + N' not found.'

raiserror(@errMsg, 16, 1);

end

RESTORE VERIFYONLY FROM DISK = @fullpath WITH FILE = @backupSetId, NOUNLOAD, NOREWIND

FETCH NEXT FROM cursor_usage INTO @dbId;

END;

CLOSE cursor_usage;

DEALLOCATE cursor_usage;

Guarantee a temp table is dropped

/*
The pupose of this post is to demonstrate the correct method of creating a temp table.
The key is to make sure that the drop statements are wrapped with an if statement and
placed both prior to table creation and after its use.
This is another argument for using temp table variables and common table expressions
*/

IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type = N'U' AND name like N'#tModelItems%') DROP TABLE #tModelItems;

CREATE TABLE #tModelItems(
ItemNbr varchar (15),
QuantityPer decimal(11,3),
GroupCode char(1),
GroupCodeDesc varchar(50),
DrawingInd char(10),
);

--Use table

IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type = N'U' AND name like N'#tModelItems%') DROP TABLE #tModelItems;


----------------
Now playing: NPR - 0808243: Car Talk 0835 August 30, 2008
via FoxyTunes

SQL Restore Database with Move

/*
This is my standard restore to a different location. This is basically the script from the help file so I don't have to find it any more.

The script assumes that the data base has been created and will be overwritten.

The first script gets the file lay out and names which needs to be inserted into the second call. It demonstrates more than one logical location in a mdf file. It can use UNC file navigation.
*/

Restore FileListonly from disk = '[File location][file name].BAK';

/*
Run the first query to get the logical name prior running the second

The file location must be relative the machine that the restore is being done so it must be a local drive as demonstrated in the sample. I have not found a way to restore from a remotely located drive.
*/

Restore Database [database name] FROM DISK = '[File location][file name].BAK'
WITH MOVE '[LogicalName]' TO 'C:\restored_databases\[filename].mdf'
, MOVE '[LogicalName_1]' To 'C:\restored_databases\[filename_1].mdf'
, MOVE '[LogicalName_log]' To 'C:\restored_databases\[filename_log].ldf'
, stats = 5
, REPLACE



----------------
Now playing: NPR - 0807195: NPR: 07-13-2008 Wait Wait... Don't Tell Me!
via FoxyTunes

SQL get the column definitions from a table

--This script is used to get the column definitions from the same table in 2 different databases
--it is primarily to demonstrate how to get column definitions from a table

DECLARE @table_name nvarchar(128)

SET @table_name = '[table_name]'

SELECT *
FROM [db_name].sys.all_objects
INNER JOIN [db_name].sys.all_columns
on all_objects.object_id = all_columns.object_id
INNER JOIN [db_name].sys.systypes
ON all_columns.system_type_id = systypes.xtype
WHERE all_objects.type = 'U'
AND all_objects.name in ( @table_name)


SELECT *
FROM [db_name_2].sys.all_objects
INNER JOIN [db_name_2].sys.all_columns
on all_objects.object_id = all_columns.object_id
INNER JOIN [db_name_2].sys.systypes
ON all_columns.system_type_id = systypes.xtype
WHERE all_objects.type = 'U'
AND all_objects.name in ( @table_name)

SQL Script to get definitions for procedures, functions, and views

-- This procedure will allow you to get the definition for the raw SQL
-- that is used to create the stored procedures, functions, and views

USE [db_name];

SELECT TOP 200
all_objects.object_id
, name
, definition
, type_desc
, type --P=Stored Procedure, V= View, FN = Function
FROM sys.all_objects
INNER JOIN sys.sql_modules
on all_objects.object_id = sql_modules.object_id
--Use the to find specific definitions
WHERE definition like '%nolock%'

Sql DDL SP audit trigger

use [db_name];


create trigger event_SP_changes
on database
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
AS
DECLARE @FullFile xml
DECLARE @EventType nvarchar(25)
DECLARE @EventTime datetime
DECLARE @LoginName nvarchar(50)
DECLARE @UserName nvarchar(25)
DECLARE @ObjectName nvarchar(50)
DECLARE @ObjectType nvarchar(25)
DECLARE @Command nvarchar(max)
SET @FullFile = eventdata()
SET @EventType = @FullFile.value('(//EVENT_INSTANCE/EventType)[1]', 'nvarchar(25)')
SET @EventTime = @FullFile.value('(//EVENT_INSTANCE/PostTime)[1]', 'DATETIME')
SET @LoginName = @FullFile.value('(//EVENT_INSTANCE/LoginName)[1]', 'nvarchar(50)')
SET @UserName = @FullFile.value('(//EVENT_INSTANCE/UserName)[1]', 'nvarchar(25)')
SET @ObjectName = @FullFile.value('(//EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(50)')
SET @ObjectType = @FullFile.value('(//EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(25)')
SET @Command = @FullFile.value('(//EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
print @command
INSERT INTO [AuditDB].dbo.db_name_SP_Events
([EventType]
,[EventTime]
,[LoginName]
,[UserName]
,[ObjectName]
,[ObjectType]
,[Command]
,[FullFile])
SELECT @EventType
, @EventTime
, @LoginName
, @UserName
, @ObjectName
, @ObjectType
, @Command
, @FullFile

SQL Purge old database backups

/*
This script works with SQL 2005 to remove old backups from a file location
xp_delete_file
is the key to this operation. This is an undocumented SQL server function.
It deletes all file older than the date given in the parameter.
I use this to implement 24 hour disk to disk backup

The TSQL assumes the full backups have an extension of BAK and transaction logs
have an extension of TRN. It will purge both transaction logs and full backups
*/

USE [master]
GO

DECLARE @tDatabases TABLE
( databaseId int
--, DATABASE_NAME varchar(25)
)

INSERT INTO @tDatabases ( databaseId )
SELECT DISTINCT s_mf.database_id
from sys.master_files s_mf
where s_mf.state = 0 -- ONLINE
AND has_dbaccess(db_name(s_mf.database_id)) = 1 -- Only look at databases to which we have access
AND s_mf.database_id <> 2 --eliminate temp db



DECLARE @dbId int;
Declare @DateToDelete datetime;
declare @DateString char(8);
DECLARE @filePath nvarchar(max);

SET @DateToDelete=DateAdd(day,-1,GetDate()); --The -1 in the date diff is 24 hours
set @DateString = Convert(nvarchar(30),@DateToDelete,1)+' '+ Convert(nvarchar(30),@DateToDelete,8); --Set up SQL string


SET @filePath = '\server_backup_directory' ;

DECLARE cursor_usage CURSOR FOR
SELECT databaseId FROM @tDatabases;

OPEN cursor_usage;

FETCH NEXT FROM cursor_usage INTO @dbId;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT db_name(@dbId);
DECLARE @execString nvarchar(max)
--Remove full backups
SET @execString = 'master.dbo.xp_delete_file 0, N''' + @filePath + db_name(@dbId) + '''' +',N''bak'' ,' + '''' + @DateString + ''''
PRINT @execString;
EXECUTE (@execString);
--Remove transaction logs
SET @execString = 'master.dbo.xp_delete_file 0, N''' + @filePath + db_name(@dbId) + '''' +',N''trn'' ,' + '''' + @DateString + ''''
PRINT @execString;
EXECUTE (@execString);

FETCH NEXT FROM cursor_usage INTO @dbId;
END;
CLOSE cursor_usage;
DEALLOCATE cursor_usage;

Reindex Stored Procedure

USE [master]
GO
/****** Object: StoredProcedure [dbo].[DbUtil_ALTER_INDEX] Script Date: 06/05/2008 16:18:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-------------------------------------------------------------------------------------------
-- OBJECT NAME : DbUtil_ALTER_INDEX
--
-- AUTHOR : Tara Kizer
-- : Alan T
--
-- DATE : February 27, 2007
-- : 2008 - 06- 06
--
-- INPUTS : @dbName - name of the database
-- @statsMode - mode to use for sys.dm_db_index_physical_stats
-- @defragType - REORGANIZE (INDEXDEFRAG) or REBUILD (DBREINDEX)
-- @minFragPercent - minimum fragmentation level
-- @maxFragPercent - maximum fragmentation level
-- @minRowCount - minimum row count
-- @PerformOnlineOnly - Forces to rebuild to be online for enterprise ed. without 1 = true
--
-- OUTPUTS : None
--
-- DEPENDENCIES : sys.dm_db_index_physical_stats, sys.objects,
-- sys.schemas, sys.indexes, sys.partitions
--
-- DESCRIPTION : Defragments indexes
/*
EXEC DbUtil_ALTER_INDEX
@dbName = 'DatabaseName',
@statsMode = 'SAMPLED',
@defragType = 'REBUILD',
@minFragPercent = 10,
@maxFragPercent = 100,
@minRowCount = 1000
, @PerformOnlineOnly = 1
*/
-------------------------------------------------------------------------------------------
CREATE PROC [dbo].[DbUtil_ALTER_INDEX]
(
@dbName sysname
, @statsMode varchar(8) = 'SAMPLED'
, @defragType varchar(10) = 'REORGANIZE'
, @minFragPercent int = 25
, @maxFragPercent int = 100
, @minRowCount int = 0
, @PerformOnlineOnly bit = 1
)
AS

SET NOCOUNT ON

DECLARE @i int
DECLARE @objectId int
DECLARE @objectName sysname
DECLARE @indexId int
DECLARE @indexName sysname
DECLARE @schemaName sysname
DECLARE @partitionNumber int
DECLARE @partitionCount int
DECLARE @recordCount bigint
DECLARE @sql nvarchar(4000)
DECLARE @edition int
DECLARE @parmDef nvarchar(500)

SELECT @i = 1, @edition = CONVERT(int, SERVERPROPERTY('EngineEdition'))

--Make sure the temp table does not exist
IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type = N'U' AND name like N'#FragIndex%') DROP TABLE #FragIndex;

SELECT
IDENTITY(int, 1, 1) AS FragIndexId,
[object_id] AS ObjectId,
index_id AS IndexId,
avg_fragmentation_in_percent AS FragPercent,
record_count AS RecordCount,
partition_number AS PartitionNumber
INTO #FragIndex
FROM sys.dm_db_index_physical_stats (DB_ID(@dbName), NULL, NULL, NULL, @statsMode)
WHERE avg_fragmentation_in_percent > @minFragPercent
AND avg_fragmentation_in_percent < @maxFragPercent AND index_id > 0
AND record_count > @minRowCount
ORDER BY ObjectId

SELECT
@objectId = ObjectId,
@indexId = IndexId,
@recordCount = RecordCount
FROM #FragIndex
WHERE FragIndexId = @i

WHILE @@ROWCOUNT <> 0
BEGIN
SET @sql = '
SELECT @objectName = o.[name], @schemaName = s.[name]
FROM ' + @dbName + '.sys.objects o
JOIN ' + @dbName + '.sys.schemas s
ON s.schema_id = o.schema_id
WHERE o.[object_id] = @objectId'

SET @parmDef = N'@objectId int, @objectName sysname OUTPUT, @schemaName sysname OUTPUT'

EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId,
@objectName = @objectName OUTPUT, @schemaName = @schemaName OUTPUT

SET @sql = 'SELECT @indexName = [name]
FROM ' + @dbName + '.sys.indexes
WHERE [object_id] = @objectId AND index_id = @indexId AND is_disabled = 0'

SET @parmDef = N'@objectId int, @indexId int, @indexName sysname OUTPUT'

EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId, @indexId = @indexId,
@indexName = @indexName OUTPUT

SET @sql = 'SELECT @partitionCount = COUNT(*)
FROM ' + @dbName + '.sys.partitions WHERE [object_id] = @objectId AND index_id = @indexId'

SET @parmDef = N'@objectId int, @indexId int, @partitionCount int OUTPUT'

EXEC sp_executesql
@sql, @parmDef, @objectId = @objectId, @indexId = @indexId,
@partitionCount = @partitionCount OUTPUT

SET @sql = 'ALTER INDEX [' + @indexName + '] ON [' + @dbName + '].[' +
@schemaName + '].[' + @objectName + '] ' + @defragType

-- Developer and Enterprise have the ONLINE = ON option for REBUILD
IF @defragType = 'REBUILD' AND @edition = 3 AND @PerformOnlineOnly = 1
SET @sql = @sql + ' WITH (ONLINE = ON)'
ELSE IF @defragType = 'REBUILD' AND @edition = 3 AND @PerformOnlineOnly = 0
SET @sql = @sql + ' WITH (ONLINE = OFF)'

IF @partitionCount > 1
SET @sql = @sql + ' PARTITION = ' + CAST(@partitionNumber AS varchar(10))

print @sql

BEGIN TRY
EXEC (@SQL)
END TRY
BEGIN CATCH
print 'ERROR1 : ' + cast(ERROR_LINE() as nvarchar(10));
print 'ERROR2 : ' + ERROR_MESSAGE();
END CATCH;


SET @i = @i + 1

SELECT
@objectId = ObjectId,
@indexId = IndexId,
@recordCount = RecordCount
FROM #FragIndex
WHERE FragIndexId = @i
END


IF EXISTS(SELECT name FROM tempdb.dbo.sysobjects WHERE type = N'U' AND name like N'#FragIndex%') DROP TABLE #FragIndex;