Export DTS to bas

Finally, after searching, reading, and testing, I found a solution. Not entirely difficult either, but could take a while depending on how many you have to export. I did 111 in an hour.

I took the GUI macro tool approach. Downloaded a macro recorder from http://www.eventcorder.com/ec_downloads.htm, used the lite version, it's free.

Install recorder
Open SQL Server enterprise manager -> get to your local packages.
Now, you'll need to use the keyboard entirely to open,save and close, so try it out first.

I used the following keystrokes, to save a package currently highlighted in the local packages, in the right pane.

  1. {ENTER}
  2. {ALT + P}
  3. {ARROW KEY DOWN}
  4. {ARROW KEY DOWN}
  5. {ENTER} #at this point you should see "saving as"
  6. {TAB}
  7. {TAB}
  8. {TAB}
  9. {ARROW KEY DOWN}
  10. {ARROW KEY DOWN}
  11. {TAB}
  12. {TAB}
  13. {ENTER} #at this point you should see "saving as" dialog box, this defaults to your "my documents" folder
  14. {ENTER}
  15. {ENTER} #File saved
  16. {CTRL + F4}
  17. {ARROW KEY DOWN} # to start on new package
Once you are satisfied this is working properly, open eventcorder, position the window so that the red circle(the record button on the far left) appears just to the right of the "Local Packages" title, which appears above the "name" column in the right pane of SQL Enterprise manager. At this point select the first package in your list, now click record(the eventcoder app will minimize to the upper left of your screen and say REC:1, or similar). Then, witout moving the mouse, just click again, this will set focuse to the SQL manager window. Now begin the key stroke sequence you practice above. Once you completed step 17, press {CTRL + ESC} to stop recording. Eventcorder should now be appearing right where it was prior to starting the record. Go to "Edit" -> "Events", here we need to set the "Seen Window" in the "Windows" tab. Hopefully you'll see 4 listing in the "Expected window" list. Click on the "Seen Window" drop down, a prompt should appear, say yes(work with previious....), now the first option selected should be the "SQL Server Enterprise Manager -[Console root..." or something similar, the main thing is that it has the class name of "MMCMainFrame".

You should be ready to go, just click "Close", focus is given back to the eventcorder app. You should see your list of packages in the back of the eventcorder app, with the second package in the list selected, if not, make it so. Click "Play" --> "Repeatedly", and watch the magic happen. One thing to note, when it gets to the end there will be an issue, since it can't key down any further than the last item, it will open the last item and play the macro again. When it tries to save, the package will prompt you to overwrite the existing package. At this point I believe I {CTRL + ESC} to end the macro. Now check you "My documents" Folder, if all went well you should be smiling. If not, ... eh, review your process, sorry.

This is as accurate as i can get at the moment, I hope this helps.... This approach worked 100% for me, and I can recreate the whole process from scracth, so I know this works.

Google and T-mobile will ROCK!!! I want an Anroid.

Just scoured the net for all info related to new phone, G1 (HTC Dream, so so) and platform released. Read a ton, for me anyway. Looks like Apple will soon have a worthy competitor out there. I predict Google's Stock is going to get crazy on oct. 22. They closed at 429.27 today.

http://www.engadget.com/2008/09/23/video-android-walkthrough-on-t-mobile-g1/

SQL 2005+ Store Proc Error Handling

--// Plop this in to query analyzer and select the DB of your choice, the F5, Enjoy.
GO

/******
Object: Table [dbo].[SQLProcErrorLogApps] Script Date: 08/28/2008 21:11:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SQLProcErrorLogApps](
[AppID] [
int] IDENTITY(1,1) NOT NULL,
[Application] [nvarchar](250)
COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Description] [nvarchar](4000)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_SQLProcErrorLogApps] PRIMARY KEY CLUSTERED
(
[AppID]
ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
)
ON [PRIMARY]

/******
Object: Table [dbo].[SQLProcErrorLog] Script Date: 08/28/2008 21:12:23 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SQLProcErrorLog](
[IDX] [
int] IDENTITY(1,1) NOT NULL,
[DateCreated] [datetime]
NOT NULL CONSTRAINT [DF_SQLProcErrorLog_DateCreated] DEFAULT (getdate()),
[AppID] [
int] NOT NULL CONSTRAINT [DF_SQLProcErrorLog_AppID] DEFAULT ((0)),
[UID] [
int] NOT NULL CONSTRAINT [DF_SQLProcErrorLog_UID] DEFAULT ((0)),
[ErrNumber] [
int] NOT NULL CONSTRAINT [DF_SQLProcErrorLog_ErrNumber] DEFAULT ((0)),
[ErrSeverity] [
int] NOT NULL CONSTRAINT [DF_SQLProcErrorLog_ErrSeverity] DEFAULT ((0)),
[ErrState] [
int] NOT NULL CONSTRAINT [DF_SQLProcErrorLog_ErrState] DEFAULT ((0)),
[ErrProc] [nvarchar](126)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ErrLine] [
int] NULL CONSTRAINT [DF_SQLProcErrorLog_ErrLine] DEFAULT ((0)),
[ErrMessage] [nvarchar](2048)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ErrDBID] [
int] NULL,
[HostName] [nvarchar](128)
COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]

GO
ALTER TABLE [dbo].[SQLProcErrorLog] WITH CHECK ADD CONSTRAINT [FK_SQLProcErrorLog_SQLProcErrorLogApps] FOREIGN KEY([AppID])
REFERENCES [dbo].[SQLProcErrorLogApps] ([AppID])
GO
ALTER TABLE [dbo].[SQLProcErrorLog] CHECK CONSTRAINT [FK_SQLProcErrorLog_SQLProcErrorLogApps]

--

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author: Bob Mihada
-- Create date: 20080828
-- Description: Template for procs
-- ViewErrors: Select * from SQLProcErrorLog
-- =============================================
CREATE PROCEDURE [dbo].[myProcIsGreat]

AS
BEGIN TRY
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE
@APPID
INT
,@UID
INT --// Any unique ID that can be used for additional debugging
,@ERRMSG NVARCHAR(2048)
,@ERRSEVERITY
INT
,@ERRSTATE
INT
,@ERRORID
INT
,@RC
INT
,@ROWAFFECTED
INT

SELECT
@APPID = 1 --// My Service
,@UID = -1 --// Nothing yet

--// Do Stuff

-- As an example I'm forcing an error
RAISERROR('Hi there, I am an error, nice to meet you.',16,1)

RETURN(321) --// My universal "All is good return code"
END TRY
BEGIN CATCH
SELECT
@ERRMSG = ERROR_MESSAGE()
,@ERRSEVERITY = ERROR_SEVERITY()
,@ERRSTATE = ERROR_STATE()

INSERT INTO SQLProcErrorlog(
AppID, UID, ErrNumber, ErrSeverity, ErrState, ErrProc, ErrLine, ErrMessage, ErrDBID, Hostname)
SELECT
@APPID
,ISNULL(@UID,-1)
,ERROR_NUMBER()
,@ERRSEVERITY
,@ERRSTATE
,ERROR_PROCEDURE()
,ERROR_LINE()
,@ERRMSG
, DB_ID()
, HOST_NAME()

RAISERROR(@ERRMSG,@ERRSEVERITY,@ERRSTATE)

RETURN(-1)
END CATCH


GO
exec('INSERT INTO [SQLProcErrorLogApps]([Application], Description) Values(''Testing Errors'', ''Blah blah blah, yakkity smakity and a tall glass of O.J.'')')
GO
EXEC myProcIsGreat
GO
Select * from SQLProcErrorLog

Health Care Costs Blows

I think Health care cost can be improved if these monkeys(gov,ipa's,any one that bills a claim) would just sit down as revisit their oldest technologies and work flows.

Batch Script to Archive files by month

Man I finally got a bit of time to write what I've desperatly need on just about every production server. Enjoy world.

  • Install http://www.7-zip.org/
  • Set the SRCDIR,DESTDIR, and then Schedule it.
@echo off
::cls%
Echo Archive my logs please...
SET ZIPAPP="C:\Program Files\7-Zip\7z.exe"
SET SRCDIR=C:\MyDirectoryFullOfCrappyLogFiles\*.Log
SET DESTDIR=E:\MyDirectoryForStoringNEWCrappyZippedLogs\

FOR %%F in (%SRCDIR%) do ( call:archivefile "%%~tF" "%%F")
ECHO BYE BYE
:archivefile
SET MD=%1

if NOT %DATE:~10,4%%Date:~4,2%%Date:~7,2%==%MD:~7,4%%MD:~1,2%%MD:~4,2% %ZIPAPP% u -tzip %DESTDIR%%MD:~7,4%%MD:~1,2%_Logs.zip %2 > nul
IF %ERRORLEVEL%==0 DEL %2



"This page is messed up, records appear to be missing or out of order."

So today I got some people telling me something appears wrong on page X. Records appear to be missing or out of order.

  1. Did anything change? No.
  2. Are you sure nothing changed? Yes.
  3. OK I'll take a look.
    • Use Profiler to capture sql command that is returning bad results.
    • Review sql code.
    • Assess if there really were any changes made recently. Found a couple variation of same proc.
    • Decipher what has changed. In conclusive.
    • Apply different sql code to rectify results. Success, but why did I have to adjust code, if it was working all this time.
    • Ah ha moment, someone added 2 columns to the table being accessed and added them to the result. So now, how does that affect us? Let's review the query plan before and after the columns were added. BINGO!
  4. The results were returned out of ordered because the original query plan use a covering index specifically for this proc. Because (someone) added 2 new columns to the results, the query plan changed and was no longer using the covering index, instead it used a lesser index which had a SORT ORDER of ASC on an IDENTITY column as oppose to the covering INDEX which had a SORT ORDER of DESC on a DateTime column.

Good day to you sir.

Note to self on Debugging a Service in Development.

Why does something keep happening when I'm debugging my service locally. When I reset an event to be triggered it almost instantly get switched. 100 strands of hair later.... OH, geezzz, the service I'm debugging locally is running on another development server and is polling the same SQL Table that I am, DUH. Ugh that drove me nuts. Man its always the little oversights that seems to bite you in the arse.

Only positive thing I can take away from this is that I won't be making this same mistake in the future.

Find Commmon SQL Objects (to drop)

We often inload a lot of data and use views to point to the most recent table. Periodically I've been deleting the old table with this script and thought I'd post it, hope it helps.

--// Find Commmon Objects

SELECT
DISTINCT
S.NAME
,'sp_helpText ''' + S.NAME +'''' [ExecToView]
,CASE
WHEN S.Type = 'P' THEN (CASE WHEN d.depid IS NOT NULL then '--//' else '' END) + 'DROP PROC ' + S.NAME
WHEN S.Type = 'U' THEN (CASE WHEN d.depid IS NOT NULL then '--//' else '' END) + 'DROP TABLE ' + S.NAME
WHEN S.Type = 'V' THEN (CASE WHEN d.depid IS NOT NULL then '--//' else '' END) + 'DROP VIEW '
ELSE 'NOT IN NORM'
END [Action]
,CASE
WHEN S.Type = 'P' THEN 'Stored Procedure'
WHEN S.Type = 'U' THEN 'User Table'
WHEN S.Type = 'V' THEN 'View'
ELSE 'NOT IN NORM'
END [ObjectType]
FROM
SYSOBJECTS S (NOLOCK)
LEFT JOIN sys.sysdepends d ON d.depid = s.id
WHERE
--S.TYPE = 'P'
S.TYPE = 'U'
--S.TYPE = 'V'
AND S.NAME LIKE '%TableNamePrefix%'
ORDER BY
S.Name

Jury duty?

The man apparently wants to see me at 7:30 am. We'll see if I get lucky.

Ah-ha, I found the positioning issue

Apparently there isn't a tower talking within my area. I know they are there, but like this site say, not all towers
http://www.cellreception.com/towers/towers.php?city=irvine&state_abr=ca
"not every antenna is required to be registered with the FCC."

In other words it ain't going to give up any positioning info?

More on Cellphone positioning

Seem to be picking up steam...

http://electricpocket.com/findme/

http://fireeagle.yahoo.net/

http://www.navizon.com/navizon_v-gps.asp

http://www.google.com/mobile/gmm/mylocation/index.html

That what I got so far, read so much that I can't keep my eyes open any longer.

Feel so behind in the game, but this ain't my bag baby. Probably already in the works or still out on the web waiting for me to stumble across but...

Develop an app/dll that just spits out your approximate position based on cellphone triangulation, like google already does. Others can publish apps for install on your phone that takes advantage of this common dll. Tired, this may not be making a whole lot of sense, but the back of my head thinks its somewhat viable.

Think this would really level the playing field and allow for so many different people to throw out as many ideas as they can with this. Allowing the public drive what they ultimately like or would like to see more of.

Beuler? This can't possible make sense, gnite.

Send wife location of what bar I'm at, so she can pick me up.
Kids send their position to parents for pickup when at friends house or soccer game moved to different field.
I was here and here a pic i just took.
Post your position to a site that will plot your wandering.
Basically, tell, find or track.

Curious and intriguing... cellphone positioning

Not sure if I'll be able to actually do anything with this info, but you never know. I've been researching this for the past 3 hours now, because google gmm on my phone isn't showing "My Location" at home. Thought it may be a bug, and wanted to to know what info it used to determine position and how to get it.

Based on "myl:MCC:MNC:LAC:CID" my first 2 values are -1,-1, so I guess something broke or is not coughing up the info?

http://maps.alphadex.de/index.php?section=mylocation
>http://maps.alphadex.de/datafiles/fct0e1b117823ccc1a.txt
>http://maps.alphadex.de/datafiles/fct0e1b11782832f02.cs
"How to get CID/LAC and stuff on a Windows Mobile device?"
>http://maps.alphadex.de/datafiles/ril.cs

Man this guy is really into it. Kudos to you Neil.

SQL2005 Find Blocking process

Yesterday my office practically had a meltdown because our site was being unresponsive. Of course, this happened right when I left for the gym.

8 missed calls later, someone taps me on the shoulder, while I'm on the treadmill at 24hour, and informs me that the site is down. Are you kidding me?? It was a co-worker who was at lunch also, they all know I go to gym at lunch. Anyway....

Dripping with sweat, I tear through the streets praying the cops are on their doughnut break. It appeared that they were too.

RDP'd into server, notice cpu maxed, 300+ active connections, and at the same time 20+ IM's from people in the office telling me the site does not appear to be working. 2 minutes into it, I find a crap load of processes being blocked, thank you sp_who2. Found process hanging everything up. Killed it, waited 5 more minutes for process to roll back, then proceeded on to damage control.

Enlight of this I needed to get the blocks on the radar(visible) for others to look at in case I'm ever not around, like today at lunch.

On to google...searching..read..read... found.

http://www.zdnetasia.com/techguide/database-management/0,3800010795,62038701,00.htm
Find blocking processes using recursion in SQL Server 2005

Adapted code into my own flavor, below, but mad props to this guy for laying the ground work.

CREATE Procedure [dbo].[sp_FindBlocks]
AS
BEGIN
SET NOCOUNT ON;
--//Find Blocking process
DECLARE @Processes TABLE (
[spid] [smallint] NOT NULL,
[BlockingSPID] [smallint] NOT NULL,
[DatabaseName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[program_name] [nchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[loginame] [nchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[ObjectName] [nvarchar](128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Definition] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Host] varchar(255) null
)

INSERT INTO @Processes
SELECT
s.spid
,s.blocked
,DB_NAME(s.dbid)
,s.program_name
,s.loginame
,OBJECT_NAME(objectid)
,LEFT(CAST(text AS VARCHAR(MAX)),100)
,s.hostname
FROM
sys.sysprocesses s
CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE
s.spid > 50;

WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow, Host, LoginName, ProgramName)
AS
(
SELECT
s.SPID
, s.BlockingSPID
, s.Definition
,ROW_NUMBER() OVER(ORDER BY s.SPID)
,0 AS LevelRow
, s.host
, s.loginame
, s.program_name
FROM
@Processes s
JOIN @Processes s1 ON s.SPID = s1.BlockingSPID
WHERE
s.BlockingSPID = 0
UNION ALL
SELECT
r.SPID
, r.BlockingSPID
, r.Definition
,d.RowNo
,d.LevelRow + 1
, d.host
, d.LoginName
, d.ProgramName
FROM
@Processes r
JOIN Blocking d ON r.BlockingSPID = d.SPID
WHERE
r.BlockingSPID > 0
)

SELECT * FROM Blocking
ORDER BY RowNo, LevelRow
END

It's still a work in process, will repost when I get it nailed down to my liking.

TTFN

GOOGLE always amazing me

About a year ago I put google maps on my T-Mobile Dash, it was a cool novelty at first. Quickly became a pain when trying to find or do anything while in route or at a stop light. It soon lost its appeal and got booted from my phone.

Today, through a series of ...
Where canI find the xml schema for my phone's home screen XML?
Searching Google.
Many sites found, nothing with true definition though.
One site had banner for software (mobiumgps.com) that would display turn by turn info on phone with bluetooth gps module connected. Dim light bulb started flickering in head.

Wasn't google working on triangulating your position based on you relative position to cell towers your phone could talk to? hmm.....

YES!!! F'n SWEET. http://www.google.com/intl/en/press/annc/20071128_maps_mobile_my_location.html. I know, I know, where the heck have I been? Very very busy.

So now I got it on my phone, AGAIN and oh man this rocks. AND then i find out about the click-to-call feature, DAMN! http://googleblog.blogspot.com/2006/11/click-to-call-in-google-maps.html

Just keeps getting better.

Now I'm waiting for someone (google), to allow me to post/send/sms/whatever my current to others. Something in me thinks the guys at Brightkite.com should look into this.

Even though it's not as accurate as GPS, with the satellite view I can move the point to my exact location, and then if I could send, MAN that would be sweet.

Privacy, privacy, privacy... I know, but really, who do I need to hide from?

"Karma is like a boomerang, never sharpen it."

Poor man's SQL Profiling

While trying to nail down a perfomance issue, then execution plan was not really offering up any clues so I had to pull this out. Did what I needed, show the hog.

/* FOR DEBUG PURPOSES */
DECLARE @pStart DateTime, @pEnd DateTime, @pElasped INT, @pDebug bit
SELECT @pDebug = 1
/*
IF @pDebug = 1 SELECT @pStart = GetDate()

...Process to time....

IF @pDebug
BEGIN
SELECT @pEnd = GetDate(), @pElasped = DateDiff(ms,@pStart, @pEnd)
PRINT status + CONVERT(NVARCHAR,@pElasped)
END
*/

SQL for the Obsessive.

If you are obsessive compulsive, look into becoming a DBA.

SQL CASE WHEN

Hey kids, how many of you use 2 or more statements to update data on the same table? Where the critieria is based on the data contained in the same table which you are trying to update? "CASE WHEN" is a wonderful alternative.

I got your Partition right here >>>

I've concurred my SQL issues regarding performance and partitioning. I've got 2 years to implement the sliding window now. NOTE: When accessing data in the partitions, yes be sure to include the partitioned column, BUT, if your column is a date like most, use BETWEEN rather than DATEDIFF. I had a rough time trying to figure out why a query plan was not using my covering index.

TTFN

SQL CLUSTERED INDEX FYI

"if the clustered index is built online by using CREATE INDEX WITH DROP_EXISTING, ONLINE=ON, all associated nonclustered indexes are re-created online also."
ref: http://technet.microsoft.com/en-us/library/ms190981.aspx

I missed that and am now paying for it with current wait time of 4 hours and 30 minutes and counting on a 112 million record partitioned table.

Watching the progress with:
SELECT
*
FROM
sys.partitions
WHERE
OBJECT_ID = OBJECT_ID('claimreport')
AND ROWS > 0
Order by
Partition_NUMBER,Rows desc, index_id

Be sureto monitor your log growth.

SQL SAFELY Shink all possible log files

--// SAFELY Shink all possible log files
--// RETURN RESULT TO TEXT. REVIEW AND EXECUTE
--// Adjust L.LogUsed AND L.LogSize as needed
SET NOCOUNT ON
If Object_Id('tempdb..#tmpLogInfo') is Not Null
Drop table #tmpLogInfo

CREATE TABLE #tmpLogInfo
(
databaseName sysname
, logSize decimal(18,5)
, logUsed decimal(18,5)
, status INT
)

INSERT INTO #tmpLogInfo
exec('dbcc sqlperf(logspace)')

SELECT
'--// ' + convert(nvarchar,L.databaseName)
+ ' LogSize: ' + convert(nvarchar,L.LogSize)
+ ' %LogUsed: ' + convert(nvarchar,L.LogUsed) + '
USE [' + convert(nvarchar,L.databaseName) + ']
' + (CASE WHEN recovery_model_desc <> 'SIMPLE' THEN '--// RECOVERY MODEL NOT SET TO SIMPLE: ' + L.databaseName + '
--// ALTER DATABASE ' + L.databaseName + ' SET RECOVERY SIMPLE

' ELSE 'DBCC SHRINKFILE (' + ms.name + ', 1)
GO' END)
FROM
#tmpLogInfo L
JOIN sys.databases db ON db.name = L.DatabaseName
JOIN master.sys.sysaltfiles ms ON ms.dbid = DB_ID(L.databaseName)
WHERE
L.databaseName not in ('master','tempdb','model','msdb')
AND ms.Status = 1048642
AND Right(ms.filename,4) = '.ldf'
AND L.LogUsed < 10

Drop table #tmpLogInfo

Comma delimeted

Comma delimited info for patients should not allow for ", jr" or ", sr". This messed up my night. Ugh, 5 records had this type of data in the lastname column. When code saw this it shifted the column to value mapping to the right by one. The mapping was being done in a SQL DTS package.

Deductive reasoning:

Code does what you tell it. You tell it what you think it should do. If the code fails they you didn't think hard enough.

Already found a few bugs, here's latest update

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_IndexMaintenance] Script Date: 04/01/2008 13:25:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* =============================================
-- Author: MK
-- Create date: 20080401
-- Description: Doesn't take partietions into account
Note create tabe "TrackFragmentation" and update
accordingly currently "[SCHEDULER].dbo.TrackFragmentation"
============================================= */
ALTER PROCEDURE [dbo].[sp_IndexMaintenance]
@DBName VarChar(50)
, @ViewType VarChar(20) = NULL --// Recent | Change
, @Rebuild_CIX bit = 0 --// 0 | 1 = True
, @Rebuild_NCIX bit = 0 --// 0 | 1 = True
, @Rebuild_ViewExecuteLoad VARCHAR(20) = 'View' --// View | Execute | Load
, @Rebuild_UsingLast BIT = 0
, @MinRows INT = 1 --// 1 - x
, @MaxRows INT = 500000000 --// 1 - x
, @MinFragPercent INT = 1 --// 1 - 100
, @MaxFragPercent INT = 90 --// 1 - 100
, @MinDensity INT = 1 --// 1- 100
, @MaxDensity INT = 90 --// 1- 100
, @ErrorMsg varchar(4000) output

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE
@DBID INT
, @DateCreated DateTime
, @IndexType VarChar(100)
, @MaintenanceAction VarChar(100)
, @RunID INT
, @Counter INT
, @Count INT
, @sql_Action VARCHAR(8000)
, @Use VARCHAR(255)
, @ErrorMessage NVARCHAR(4000)
, @Msg NVARCHAR(1000)
, @RC_SUCCESSFUL INT
, @RC_FAILED INT
, @RowsAffected INT
, @Error INT

DECLARE @tmp TABLE (idx int identity(1,1), sql_action VarChar(8000), TableName VarChar(255))

SELECT
@RC_SUCCESSFUL =320
,@RC_FAILED = -1
,@RowsAffected = 0
,@MaintenanceAction = 'NOTHING'
,@Msg = ''

/*
Perform validation
*/

SELECT @MaintenanceAction = 'LOAD' --//Default

--// What do you want to do
IF LEN(ISNULL(@ViewType,'~')) > 1
BEGIN
IF @ViewType NOT IN ('RECENT','CHANGE')
BEGIN
SELECT @Msg = 'Invalid @ViewType: ' + @ViewType
GOTO PROBLEM
END

SELECT @MaintenanceAction = 'VIEW'
END
ELSE IF (@Rebuild_CIX = 1 OR @Rebuild_NCIX = 1)
BEGIN

IF ISNULL(@Rebuild_ViewExecuteLoad,'~') NOT IN ('VIEW','EXECUTE','LOAD')
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid @Rebuild_ViewOrExecute: ' + @Rebuild_ViewExecuteLoad

IF NOT ISNULL(@MinFragPercent,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MinFragPercent' + LTRIM(str(@MinFragPercent))

IF NOT ISNULL(@MaxFragPercent,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MaxFragPercent' + LTRIM(str(@MaxFragPercent))

IF NOT ISNULL(@MinDensity,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MinDensity' + LTRIM(str(@MinDensity))

IF NOT ISNULL(@MaxDensity,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MaxDensity' + LTRIM(str(@MaxDensity))

IF @Rebuild_CIX = 1 AND @Rebuild_NCIX = 1
SELECT @Msg = @Msg + char(10) + char(13) + 'You can only Rebuild either NONCLUSTERED or CLUSTERED, not both'

IF ISNULL(@MinRows,0) <= 0
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MinRows' + LTRIM(str(@MinRows))

IF ISNULL(@MaxRows,0) <= 0
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MaxRows' + LTRIM(str(@MaxRows))



IF LEN(ISNULL(@Msg,'')) > 1 GOTO PROBLEM

END
ELSE IF ISNULL(@Rebuild_ViewExecuteLoad,'~') <> 'LOAD'
BEGIN
SELECT @Msg = 'What do you want to do?'
GOTO PROBLEM
END

SELECT
@DBID = DB_ID(@DBName)
,@DateCreated = GetDate()

IF @DBID IS NULL
BEGIN
SELECT @Msg = 'Invalid : @DBName' + @DBName
GOTO PROBLEM
END

IF @MaintenanceAction = 'VIEW'
BEGIN
IF @ViewType = 'CHANGE'
BEGIN
-- View Frag change
SELECT
A.DBName
, A.TableName
, A.IndexName
, A.IndexType
, CAST(ROUND(A.current_density_percentage,2) AS FLOAT) [Now Dens]
, CAST(ROUND( B.current_density_percentage,2) AS FLOAT) [Prev Dens]
, CAST(ROUND(A.current_fragmentation_percentage,2) AS FLOAT) [Now %Frag]
, CAST(ROUND(B.current_fragmentation_percentage,2) AS FLOAT) [Prev %Frag]
, CAST(ROUND(A.MBUsed,2) AS FLOAT) [Now MB]
, CAST(ROUND(B.MBUsed,2) AS FLOAT) [Prev MB]
, A.Rows [Now Rows]
, B.Rows [Prev Rows]
,Max(A.RunID) [Now RunID]
,A.DateCreated [Now RunTime]
,Max(B.RunID) [Prev RunID]
,B.DateCreated [Prev RunTime]
FROM
[SCHEDULER].dbo.TrackFragmentation A
LEFT JOIN [SCHEDULER].dbo.TrackFragmentation B ON B.TableName = A.Tablename AND B.IndexName = A.IndexName
WHERE
A.RunID > B.RunID
AND A.DBName = @DBName
GROUP BY A.DBName
, A.TableName
, A.IndexName
, A.IndexType
, CAST(ROUND(A.current_density_percentage,2) AS FLOAT)
, CAST(ROUND( B.current_density_percentage,2) AS FLOAT)
, CAST(ROUND(A.current_fragmentation_percentage,2) AS FLOAT)
, CAST(ROUND(B.current_fragmentation_percentage,2) AS FLOAT)
, CAST(ROUND(A.MBUsed,2) AS FLOAT)
, CAST(ROUND(B.MBUsed,2) AS FLOAT)
, A.Rows
, B.Rows
, A.DateCreated
, B.DateCreated
Order by Max(A.RunID) DESC, MAX(B.RunID) DESC, A.IndexType DESC,A.[Rows] ASC, A.TableName, A.IndexName
END
ELSE
BEGIN
SELECT
A.DBName
, A.TableName
, A.IndexName
, A.IndexType
, CAST(ROUND(A.current_density_percentage,2) AS FLOAT) [Now Dens]
, CAST(ROUND(A.current_fragmentation_percentage,2) AS FLOAT) [Now %Frag]
, CAST(ROUND(A.MBUsed,2) AS FLOAT) [Now MB]
, A.Rows [Now Rows]
, A.RunID [Now RunID]
,A.DateCreated [Now RunTime]
FROM
[SCHEDULER].dbo.TrackFragmentation A
WHERE
A.RunID = (SELECT Max(RunID) FROM [SCHEDULER].dbo.TrackFragmentation WHERE DBName = @DBName)
Order by A.IndexType DESC,A.[Rows] ASC, A.TableName, A.IndexName
END

GOTO EXIT_PROC
END

IF @Rebuild_UsingLast = 1
BEGIN
SELECT
@RunID = MAX(RunID)
FROM
[SCHEDULER].dbo.TrackFragmentation WHERE DBName = @DBName

IF @@RowCount = 1 GOTO REBUILD_USINGLAST

SELECT @Msg = @Msg + char(10) + char(13) + 'There is not existing data for this DB ' + @DBName
GOTO PROBLEM
END

--// Load TrackFragmentation
SELECT
@RunID = MAX(RunID) + 1
FROM
[SCHEDULER].dbo.TrackFragmentation

IF @RunID IS NULL SELECT @RunID = 1

PRINT 'Loading TrackFragmentation'

SELECT @sql_action = '
USE ' + QUOTENAME(@DBName) + '
IF object_id(''tempdb..##tmpSysIndex'') IS NOT NULL
BEGIN
DROP TABLE ##tmpSysIndex
END
SELECT OBJECT_NAME(object_id) TableName, * INTO ##tmpSysIndex FROM ' + QUOTENAME(@DBName) + '.sys.indexes WHERE [Name] is not null

IF object_id(''tempdb..##tmp_dboSysindex'') IS NOT NULL
BEGIN
DROP TABLE ##tmp_dboSysindex
END
SELECT * INTO ##tmp_dboSysindex FROM ' + QUOTENAME(@DBName) + '.dbo.sysindexes WHERE groupid >0'

EXEC(@sql_action)

INSERT INTO [SCHEDULER].dbo.TrackFragmentation(
DBName
, TableName
, IndexName
, IndexType
, DataStoreType
, partition_number
, current_density_percentage
, current_fragmentation_percentage
, MBUsed
, Rows
, [object_id]
, index_id
, DateCreated
, RunID
)
SELECT
DB_NAME(@DBID) --// AS DBName
,I.TableName --// AS TableName
,I.name AS [IndexName]
,I.type_desc --// AS [IndexType] REPLACE(p.index_type_desc,' INDEX','')
,p.[alloc_unit_type_desc] --// AS DataStoreType
,CAST(p.[partition_number] AS varchar(10)) --// AS [partition_number]
,p.[avg_page_space_used_in_percent] --// AS [current_density_percentage]
,p.[avg_fragmentation_in_percent] --// AS current_fragmentation_percentage
,CASE WHEN page_count <= 8 THEN 0 ELSE (page_count * 8.0 / 1024.0) END --// AS [MBUsed]
,(SELECT top 1 rows FROM ##tmp_dboSysindex WHERE id = p.object_id AND indid < 2) --// [Rows]
,P.[object_id]
,P.[index_id]
,@DateCreated --// AS DateCreated
,@RunID --// AS RunID
--INTO TrackFragmentation --// If you need to re-create
FROM
sys.dm_db_index_physical_stats( @DBID, NULL, NULL, NULL, 'SAMPLED') p
JOIN ##tmpSysIndex i (NOLOCK) ON I.[object_id] = p.[object_id] AND I.[index_id] = p.[index_id]
WHERE
p.index_id > 0

PRINT 'Loaded: ' + LTRIM(STR(@@ROWCOUNT)) + ' records'

REBUILD_USINGLAST:

--// When I try to exclude this in main query it takes longer
UPDATE [SCHEDULER].dbo.TrackFragmentation
SET RunOnline = 0
WHERE [object_ID] IN (SELECT [object_id] FROM [SCHEDULER].dbo.TrackFragmentation T WHERE DataStoreType = 'LOB_DATA')

DELETE FROM [SCHEDULER].dbo.TrackFragmentation WHERE DataStoreType = 'LOB_DATA' AND RunID = @RunID

--// Drop Temp tables
IF object_id('tempdb..##tmpSysIndex') IS NOT NULL DROP TABLE ##tmpSysIndex
IF object_id('tempdb..##tmp_dboSysindex') IS NOT NULL DROP TABLE ##tmp_dboSysindex

IF @Rebuild_ViewExecuteLoad = 'LOAD' GOTO EXIT_PROC

IF @Rebuild_NCIX = 1
BEGIN
SET @IndexType = 'NONCLUSTERED'
END
ELSE
BEGIN
SET @IndexType = 'CLUSTERED'
END

SELECT TOP 1
@Use = 'USE ' + QUOTENAME(DBName)
FROM
[SCHEDULER].dbo.TrackFragmentation (NOLOCK)
WHERE
RunID = @RunID

INSERT INTO @tmp
SELECT @Use, NULL

INSERT INTO @tmp
SELECT
'PRINT ''--//' + QUOTENAME(DBName) + '.' + QUOTENAME(TableName) + '..' + QUOTENAME(IndexName) +
' SIZE: ' + Ltrim(str([MBUsed])) + '(mb) ' +
' Rows: ' + Ltrim(str([Rows])) +
' %Frag: ' + Ltrim(str(Cast(round(current_fragmentation_percentage,0) as int))) +
' Density: ' + Ltrim(str(Cast(round(current_density_percentage,0) as int))) + '''' + CHAR(13) + CHAR(10)
+ CASE WHEN [Rows] > 1000000 THEN 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(DBName) + '..' + QUOTENAME(TableName) + ' REORGANIZE'
ELSE 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(DBName) + '..' + QUOTENAME(TableName) + ' REBUILD WITH (ONLINE=' + CASE WHEN RunOnline = 0 THEN 'OFF' ELSE 'ON' END +', SORT_IN_TEMPDB=ON) ' END + '
UPDATE STATISTICS ' + QUOTENAME(TableName) + QUOTENAME(IndexName)
, QUOTENAME(TableName)
FROM
[SCHEDULER].dbo.TrackFragmentation (NOLOCK)
WHERE
(
(current_fragmentation_percentage BETWEEN @MinFragPercent AND @MaxFragPercent)
OR
(current_density_percentage BETWEEN @MinDensity AND @MaxDensity)
)
AND Rows BETWEEN @MinRows AND @MaxRows
AND IndexType = @IndexType
AND RunID = @RunID
ORDER BY
IndexType DESC
,Rows ASC
,TableName

INSERT INTO @tmp
SELECT
'EXEC sp_recompile ''' + TableName + ''''+ CHAR(13) + CHAR(10),NULL
FROM
@tmp
GROUP BY
TableName

SELECT @Counter = 0, @Count = Count(*) FROM @tmp

WHILE ISNULL(@Counter,0) < @Count
BEGIN
SELECT @Counter = @Counter + 1

SELECT @sql_Action = sql_Action FROM @tmp WHERE IDX = @Counter

IF @Rebuild_ViewExecuteLoad = 'Execute'
BEGIN
SELECT @sql_Action = @Use + char(10) + char(13) + @sql_Action
EXEC(@sql_Action)
END
ELSE
BEGIN
PRINT @sql_Action
END
END


EXIT_PROC:
RETURN(@RC_SUCCESSFUL)

PROBLEM:
SELECT @ErrorMsg = 'Stored Procedure: sp_IndexMaintenance
Error Message: ' + @Msg + '
Input Parameter(s):
@DBName = ' + isnull( CONVERT(nvarchar,@DBName), '' ) + '
@ViewType = ' + isnull( CONVERT(nvarchar,@ViewType), '' ) + '
@Rebuild_CIX = ' + isnull( CONVERT(nvarchar,@Rebuild_CIX), '' ) + '
@Rebuild_NCIX = ' + isnull( CONVERT(nvarchar,@Rebuild_NCIX), '' ) + '
@Rebuild_ViewExecuteLoad = ' + isnull( CONVERT(nvarchar,@Rebuild_ViewExecuteLoad), '' ) + '
@MinFragPercent = ' + isnull( CONVERT(nvarchar,@MinFragPercent), '' ) + '
@MaxFragPercent = ' + isnull( CONVERT(nvarchar,@MaxFragPercent), '' ) + '
@MinDensity = ' + isnull( CONVERT(nvarchar,@MinDensity), '' ) + '
@MaxDensity = ' + isnull( CONVERT(nvarchar,@MaxDensity), '' )
SELECT @ErrorMsg
RAISERROR(@ErrorMsg, 16,1) WITH LOG
RETURN(@RC_FAILED)

END

Here's the Create for TrackFragmentation table

USE [scheduler]
GO
/****** Object: Table [dbo].[TrackFragmentation] Script Date: 04/01/2008 19:06:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[TrackFragmentation](
[DBName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TableName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IndexName] [nvarchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IndexType] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[DataStoreType] [nvarchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[partition_number] [varchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[current_density_percentage] [float] NULL,
[current_fragmentation_percentage] [float] NULL,
[MBUsed] [numeric](29, 7) NULL,
[Rows] [int] NULL,
[object_id] [int] NULL,
[index_id] [int] NULL,
[RunOnline] [bit] NULL CONSTRAINT [DF_TrackFragmentation_RunOnline] DEFAULT ((1)),
[DateCreated] [datetime] NULL,
[RunID] [int] NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

mmmm Nice tidy interface

Oh my!!! A sense of accomplishment. Now I can do just about anything with my indexes, schedule rebuild, monitor changes, create an alert if the delta is out of line.

DECLARE @return_value int,
@ErrorMsg varchar(4000)

EXEC @return_value = [dbo].[sp_IndexMaintenance]
@DBName = N'MyDB',
@ViewType = N'',
@Rebuild_CIX = 0,
@Rebuild_NCIX = 1,
@Rebuild_ViewExecuteLoad = 'LOAD',
@Rebuild_UsingLast = 0,
@MinRows = 1,
@MaxRows = 1000000,
@MinFragPercent = 3,
@MaxFragPercent = 90,
@MinDensity = 1,
@MaxDensity = 90,
@ErrorMsg = @ErrorMsg OUTPUT

I think I got it sp_IndexMaintenance

not entirely an original work, see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2865707&SiteID=1.

But I'm not very fond of just copy, paste, and pray. I wanted to ensure I fully understood what was going on. Also adapted it to fit my specific needs. But I would also hope this is a wee bit easier to grasp. Let me know if i'm missed something.

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_IndexMaintenance] Script Date: 04/01/2008 13:25:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/* =============================================
-- Author: BOBMIHADA
-- Create date: 20080401
-- Description: Doesn't take partietions into account
Note create tabe "TrackFragmentation" and update
accordingly currently "[SCHEDULER].dbo.TrackFragmentation"
============================================= */
ALTER PROCEDURE [dbo].[sp_IndexMaintenance]
@DBName VarChar(50)
, @ViewType VarChar(20) = NULL --// Recent | Change
, @Rebuild_CIX bit = 0 --// 0 | 1 = True
, @Rebuild_NCIX bit = 0 --// 0 | 1 = True
, @Rebuild_ViewExecuteLoad VARCHAR(20) = 'View' --// View | Execute | Load
, @Rebuild_UsingLast BIT = 0
, @MinRows INT = 1 --// 1 - x
, @MaxRows INT = 500000000 --// 1 - x
, @MinFragPercent INT = 1 --// 1 - 100
, @MaxFragPercent INT = 90 --// 1 - 100
, @MinDensity INT = 1 --// 1- 100
, @MaxDensity INT = 90 --// 1- 100
, @ErrorMsg varchar(4000) output

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE
@DBID INT
, @DateCreated DateTime
, @IndexType VarChar(100)
, @MaintenanceAction VarChar(100)
, @RunID INT
, @Counter INT
, @Count INT
, @sql_Action VARCHAR(8000)
, @Use VARCHAR(255)
, @ErrorMessage NVARCHAR(4000)
, @Msg NVARCHAR(1000)
, @RC_SUCCESSFUL INT
, @RC_FAILED INT
, @RowsAffected INT
, @Error INT

DECLARE @tmp TABLE (idx int identity(1,1), sql_action VarChar(8000), TableName VarChar(255))

SELECT
@RC_SUCCESSFUL =320
,@RC_FAILED = -1
,@RowsAffected = 0
,@MaintenanceAction = 'NOTHING'
,@Msg = ''

/*
Perform validation
*/

SELECT @MaintenanceAction = 'LOAD' --//Default

--// What do you want to do
IF LEN(ISNULL(@ViewType,'~')) > 1
BEGIN
IF @ViewType NOT IN ('RECENT','CHANGE')
BEGIN
SELECT @Msg = 'Invalid @ViewType: ' + @ViewType
GOTO PROBLEM
END

SELECT @MaintenanceAction = 'VIEW'
END
ELSE IF (@Rebuild_CIX = 1 OR @Rebuild_NCIX = 1)
BEGIN

IF ISNULL(@Rebuild_ViewExecuteLoad,'~') NOT IN ('VIEW','EXECUTE','LOAD')
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid @Rebuild_ViewOrExecute: ' + @Rebuild_ViewExecuteLoad

IF NOT ISNULL(@MinFragPercent,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MinFragPercent' + LTRIM(str(@MinFragPercent))

IF NOT ISNULL(@MaxFragPercent,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MaxFragPercent' + LTRIM(str(@MaxFragPercent))

IF NOT ISNULL(@MinDensity,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MinDensity' + LTRIM(str(@MinDensity))

IF NOT ISNULL(@MaxDensity,0) BETWEEN 1 AND 100
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MaxDensity' + LTRIM(str(@MaxDensity))

IF @Rebuild_CIX = 1 AND @Rebuild_NCIX = 1
SELECT @Msg = @Msg + char(10) + char(13) + 'You can only Rebuild either NONCLUSTERED or CLUSTERED, not both'

IF ISNULL(@MinRows,0) <= 0
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MinRows' + LTRIM(str(@MinRows))

IF ISNULL(@MaxRows,0) <= 0
SELECT @Msg = @Msg + char(10) + char(13) + 'Invalid : @MaxRows' + LTRIM(str(@MaxRows))



IF LEN(ISNULL(@Msg,'')) > 1 GOTO PROBLEM

END
ELSE IF ISNULL(@Rebuild_ViewExecuteLoad,'~') <> 'LOAD'
BEGIN
SELECT @Msg = 'What do you want to do?'
GOTO PROBLEM
END

SELECT
@DBID = DB_ID(@DBName)
,@DateCreated = GetDate()

IF @DBID IS NULL
BEGIN
SELECT @Msg = 'Invalid : @DBName' + @DBName
GOTO PROBLEM
END

IF @MaintenanceAction = 'VIEW'
BEGIN
IF @ViewType = 'CHANGE'
BEGIN
-- View Frag change
SELECT
A.DBName
, A.TableName
, A.IndexName
, A.IndexType
, CAST(ROUND(A.current_density_percentage,2) AS FLOAT) [Now Dens]
, CAST(ROUND( B.current_density_percentage,2) AS FLOAT) [Prev Dens]
, CAST(ROUND(A.current_fragmentation_percentage,2) AS FLOAT) [Now %Frag]
, CAST(ROUND(B.current_fragmentation_percentage,2) AS FLOAT) [Prev %Frag]
, CAST(ROUND(A.MBUsed,2) AS FLOAT) [Now MB]
, CAST(ROUND(B.MBUsed,2) AS FLOAT) [Prev MB]
, A.Rows [Now Rows]
, B.Rows [Prev Rows]
,Max(A.RunID) [Now RunID]
,A.DateCreated [Now RunTime]
,Max(B.RunID) [Prev RunID]
,B.DateCreated [Prev RunTime]
FROM
[SCHEDULER].dbo.TrackFragmentation A
LEFT JOIN [SCHEDULER].dbo.TrackFragmentation B ON B.TableName = A.Tablename AND B.IndexName = A.IndexName
WHERE
A.RunID > B.RunID
AND A.DBName = @DBName
GROUP BY A.DBName
, A.TableName
, A.IndexName
, A.IndexType
, CAST(ROUND(A.current_density_percentage,2) AS FLOAT)
, CAST(ROUND( B.current_density_percentage,2) AS FLOAT)
, CAST(ROUND(A.current_fragmentation_percentage,2) AS FLOAT)
, CAST(ROUND(B.current_fragmentation_percentage,2) AS FLOAT)
, CAST(ROUND(A.MBUsed,2) AS FLOAT)
, CAST(ROUND(B.MBUsed,2) AS FLOAT)
, A.Rows
, B.Rows
, A.DateCreated
, B.DateCreated
Order by A.IndexType DESC,A.[Rows] ASC, A.TableName, A.IndexName
END
ELSE
BEGIN
SELECT
A.DBName
, A.TableName
, A.IndexName
, A.IndexType
, CAST(ROUND(A.current_density_percentage,2) AS FLOAT) [Now Dens]
, CAST(ROUND(A.current_fragmentation_percentage,2) AS FLOAT) [Now %Frag]
, CAST(ROUND(A.MBUsed,2) AS FLOAT) [Now MB]
, A.Rows [Now Rows]
, A.RunID [Now RunID]
,A.DateCreated [Now RunTime]
FROM
[SCHEDULER].dbo.TrackFragmentation A
WHERE
A.DBName =@DBName
AND A.RunID = (SELECT Max(RunID) FROM [SCHEDULER].dbo.TrackFragmentation WHERE A.DBName = @DBName)
Order by A.IndexType DESC,A.[Rows] ASC, A.TableName, A.IndexName
END

GOTO EXIT_PROC
END

IF @Rebuild_UsingLast = 1
BEGIN
SELECT
@RunID = MAX(RunID)
FROM
[SCHEDULER].dbo.TrackFragmentation WHERE DBName = @DBName

IF @@RowCount = 1 GOTO REBUILD_USINGLAST

SELECT @Msg = @Msg + char(10) + char(13) + 'There is not existing data for this DB ' + @DBName
GOTO PROBLEM
END

--// Load TrackFragmentation
SELECT
@RunID = MAX(RunID) + 1
FROM
[SCHEDULER].dbo.TrackFragmentation

IF @RunID IS NULL SELECT @RunID = 1

PRINT 'Loading TrackFragmentation'

SELECT @sql_action = '
USE ' + QUOTENAME(@DBName) + '
IF object_id(''tempdb..##tmpSysIndex'') IS NOT NULL
BEGIN
DROP TABLE ##tmpSysIndex
END
SELECT OBJECT_NAME(object_id) TableName, * INTO ##tmpSysIndex FROM ' + QUOTENAME(@DBName) + '.sys.indexes WHERE [Name] is not null

IF object_id(''tempdb..##tmp_dboSysindex'') IS NOT NULL
BEGIN
DROP TABLE ##tmp_dboSysindex
END
SELECT * INTO ##tmp_dboSysindex FROM ' + QUOTENAME(@DBName) + '.dbo.sysindexes WHERE groupid >0'

EXEC(@sql_action)

INSERT INTO [SCHEDULER].dbo.TrackFragmentation(
DBName
, TableName
, IndexName
, IndexType
, DataStoreType
, partition_number
, current_density_percentage
, current_fragmentation_percentage
, MBUsed
, Rows
, [object_id]
, index_id
, DateCreated
, RunID
)
SELECT
DB_NAME(@DBID) --// AS DBName
,I.TableName --// AS TableName
,I.name AS [IndexName]
,I.type_desc --// AS [IndexType] REPLACE(p.index_type_desc,' INDEX','')
,p.[alloc_unit_type_desc] --// AS DataStoreType
,CAST(p.[partition_number] AS varchar(10)) --// AS [partition_number]
,p.[avg_page_space_used_in_percent] --// AS [current_density_percentage]
,p.[avg_fragmentation_in_percent] --// AS current_fragmentation_percentage
,CASE WHEN page_count <= 8 THEN 0 ELSE (page_count * 8.0 / 1024.0) END --// AS [MBUsed]
,(SELECT top 1 rows FROM ##tmp_dboSysindex WHERE id = p.object_id AND indid < 2) --// [Rows]
,P.[object_id]
,P.[index_id]
,@DateCreated --// AS DateCreated
,@RunID --// AS RunID
--INTO TrackFragmentation --// If you need to re-create
FROM
sys.dm_db_index_physical_stats( @DBID, NULL, NULL, NULL, 'SAMPLED') p
JOIN ##tmpSysIndex i (NOLOCK) ON I.[object_id] = p.[object_id] AND I.[index_id] = p.[index_id]
WHERE
p.index_id > 0

PRINT 'Loaded: ' + LTRIM(STR(@@ROWCOUNT)) + ' records'

REBUILD_USINGLAST:

--// When I try to exclude this in main query it takes longer
UPDATE [SCHEDULER].dbo.TrackFragmentation
SET RunOnline = 0
WHERE [object_ID] IN (SELECT [object_id] FROM [SCHEDULER].dbo.TrackFragmentation T WHERE DataStoreType = 'LOB_DATA')

DELETE FROM [SCHEDULER].dbo.TrackFragmentation WHERE DataStoreType = 'LOB_DATA' AND RunID = @RunID

--// Drop Temp tables
IF object_id('tempdb..##tmpSysIndex') IS NOT NULL DROP TABLE ##tmpSysIndex
IF object_id('tempdb..##tmp_dboSysindex') IS NOT NULL DROP TABLE ##tmp_dboSysindex

IF @Rebuild_ViewExecuteLoad = 'LOAD' GOTO EXIT_PROC

IF @Rebuild_NCIX = 1
BEGIN
SET @IndexType = 'NONCLUSTERED'
END
ELSE
BEGIN
SET @IndexType = 'CLUSTERED'
END

SELECT TOP 1
@Use = 'USE ' + QUOTENAME(DBName)
FROM
[SCHEDULER].dbo.TrackFragmentation (NOLOCK)
WHERE
RunID = @RunID

INSERT INTO @tmp
SELECT @Use, NULL

INSERT INTO @tmp
SELECT
'PRINT ''--//' + QUOTENAME(DBName) + '.' + QUOTENAME(TableName) + '..' + QUOTENAME(IndexName) +
' SIZE: ' + Ltrim(str([MBUsed])) + '(mb) ' +
' Rows: ' + Ltrim(str([Rows])) +
' %Frag: ' + Ltrim(str(Cast(round(current_fragmentation_percentage,0) as int))) +
' Density: ' + Ltrim(str(Cast(round(current_density_percentage,0) as int))) + '''' + CHAR(13) + CHAR(10)
+ CASE WHEN [Rows] > 1000000 THEN 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(DBName) + '..' + QUOTENAME(TableName) + ' REORGANIZE'
ELSE 'ALTER INDEX ' + QUOTENAME(IndexName) + ' ON ' + QUOTENAME(DBName) + '..' + QUOTENAME(TableName) + ' REBUILD WITH (ONLINE=' + CASE WHEN RunOnline = 0 THEN 'OFF' ELSE 'ON' END +', SORT_IN_TEMPDB=ON) ' END + '
UPDATE STATISTICS ' + QUOTENAME(TableName) + QUOTENAME(IndexName)
, QUOTENAME(TableName)
FROM
[SCHEDULER].dbo.TrackFragmentation (NOLOCK)
WHERE
(
(current_fragmentation_percentage BETWEEN @MinFragPercent AND @MaxFragPercent)
OR
(current_density_percentage BETWEEN @MinDensity AND @MaxDensity)
)
AND Rows BETWEEN @MinRows AND @MaxRows
AND IndexType = @IndexType
AND RunID = @RunID
ORDER BY
IndexType DESC
,Rows ASC
,TableName

INSERT INTO @tmp
SELECT
'EXEC sp_recompile ''' + TableName + ''''+ CHAR(13) + CHAR(10),NULL
FROM
@tmp
GROUP BY
TableName

SELECT @Counter = 0, @Count = Count(*) FROM @tmp

WHILE ISNULL(@Counter,0) < @Count
BEGIN
SELECT @Counter = @Counter + 1

SELECT @sql_Action = sql_Action FROM @tmp WHERE IDX = @Counter

IF @Rebuild_ViewExecuteLoad = 'Execute'
BEGIN
SELECT @sql_Action = @Use + char(10) + char(13) + @sql_Action
EXEC(@sql_Action)
END
ELSE
BEGIN
PRINT @sql_Action
END
END


EXIT_PROC:
RETURN(@RC_SUCCESSFUL)

PROBLEM:
SELECT @ErrorMsg = 'Stored Procedure: sp_IndexMaintenance
Error Message: ' + @Msg + '
Input Parameter(s):
@DBName = ' + isnull( CONVERT(nvarchar,@DBName), '' ) + '
@ViewType = ' + isnull( CONVERT(nvarchar,@ViewType), '' ) + '
@Rebuild_CIX = ' + isnull( CONVERT(nvarchar,@Rebuild_CIX), '' ) + '
@Rebuild_NCIX = ' + isnull( CONVERT(nvarchar,@Rebuild_NCIX), '' ) + '
@Rebuild_ViewExecuteLoad = ' + isnull( CONVERT(nvarchar,@Rebuild_ViewExecuteLoad), '' ) + '
@MinFragPercent = ' + isnull( CONVERT(nvarchar,@MinFragPercent), '' ) + '
@MaxFragPercent = ' + isnull( CONVERT(nvarchar,@MaxFragPercent), '' ) + '
@MinDensity = ' + isnull( CONVERT(nvarchar,@MinDensity), '' ) + '
@MaxDensity = ' + isnull( CONVERT(nvarchar,@MaxDensity), '' )
SELECT @ErrorMsg
RAISERROR(@ErrorMsg, 16,1) WITH LOG
RETURN(@RC_FAILED)

END

Brain in a daze

CREATE PROCEDURE sp_IndexMaintenance
@DBName VarChar(50)
, @ViewType VarChar(20) = NULL --// Recent | Change
, @Rebuild_CIX bit = 0 --// 0 | 1 = True
, @Rebuild_NCIX bit = 0 --// 0 | 1 = True
, @Rebuild_ViewOrExecute = 'View' --// View | Execute
, @MinFragPercent INT = 1 --// 1 - 100
, @MaxFragPercent INT = 90 --// 1 - 100
, @MinDensity INT = 1 --// 1- 100
, @MaxDensity INT = 90 --// 1- 100
, @ErrorMsg varchar(4000) output