Select from table and alter result

  • How do I select from a SQL Table and change the result?

    For example..

    declare FindFragment cursor for

    SELECT object_id, name FROM #IndexFrag WHERE (avg_fragmentation_In_Percent > 5) and (avg_fragmentation_In_Percent < 40) and (name not like 'null')

    --Cursor to go through each index which are between 5% and 40% fragmented and rebuild

    open FindFragment

    fetch next from FindFragment into @ObjectID, @result

    I want to change the 'name' field I have selected from a temporary table, which is put into @result in the cursor to have closed brackets around it.

  • If you are developing an in-house index degfragmentation tool, I personally wouldn't invest the time reinventing the wheel. There are several options already developed and freely available on the web.

    We use this one in house, courtesy of Ola Hallengren.

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

  • declare FindFragment cursor for

    SELECT object_id, '['+name+']' FROM #IndexFrag WHERE (avg_fragmentation_In_Percent > 5) and (avg_fragmentation_In_Percent < 40) and (name not like 'null')

    Or don't spend time re-inventing the wheel and use http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • It was working fine until it came across an index on a view earlier, since i referenced sys.tables instead of sys.objects it didn't pick these up. This is now resolved. Then I noticed it didn't take into account if an index had a '.' in the name of it.. so this is where this change has come from..

    --Create temp table for list of indexes

    CREATE TABLE #IndexFrag(

    database_id int,

    object_ID int,

    index_id int,

    name ntext,

    page_count int,

    avg_fragmentation_In_Percent real )

    --Fill the table with indexes with a page count higher than 10 and fragmented more than 5%

    insert into #IndexFrag (database_id, object_ID, index_id, name, page_count, avg_fragmentation_In_Percent)

    SELECT ps.database_id, ps.OBJECT_ID,

    ps.index_id, b.name, ps.page_count,

    ps.avg_fragmentation_in_percent

    FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS ps

    INNER JOIN sys.indexes AS b ON ps.OBJECT_ID = b.OBJECT_ID

    AND ps.index_id = b.index_id

    WHERE ps.database_id = DB_ID() and ps.page_count > 10 and ps.avg_fragmentation_in_percent > 5

    ORDER BY ps.OBJECT_ID

    --Selecting all index's between 5% and 40% fragmented

    declare @cnt int

    declare @Result varchar(200)

    declare @cmd nvarchar(500)

    declare @tablename nvarchar(500)

    declare @objectID int

    declare FindFragment cursor for

    SELECT object_id, '['+name+']' FROM #IndexFrag WHERE (avg_fragmentation_In_Percent > 5) and (avg_fragmentation_In_Percent < 40) and (name not like 'null')

    --Cursor to go through each index which are between 5% and 40% fragmented and rebuild

    open FindFragment

    fetch next from FindFragment into @ObjectID, @result

    while @@fetch_status = 0

    BEGIN

    select @tablename = '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' FROM sys.indexes i

    INNER JOIN sys.objects AS t ON i.object_id = t.object_id WHERE i.name = @Result AND i.object_id = @objectID

    set @cmd = N'ALTER Index ' + @result + ' on ' + @tablename + ' REORGANIZE '

    print @cmd

    --EXEC sp_executeSQL @cmd

    fetch next from FindFragment into @objectID, @result

    END

    close FindFragment

    deallocate FindFragment

    --drop table #IndexFrag

    declare FindFragment cursor for

    SELECT object_id, '['+name+']' FROM #IndexFrag WHERE (avg_fragmentation_In_Percent > 40) and (name not like 'null')

    --Cursor to go through each index which are over 40% fragmented and rebuild

    open FindFragment

    fetch next from FindFragment into @objectID, @result

    while @@fetch_status = 0

    BEGIN

    select @tablename = '[' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + ']' FROM sys.indexes i

    INNER JOIN sys.objects AS t ON i.object_id = t.object_id WHERE i.name = @Result AND i.object_id = @objectID

    set @cmd = N'ALTER Index ' + @result + ' on ' + @tablename + ' REBUILD '

    PRINT @cmd

    --EXEC sp_executeSQL @cmd

    fetch next from FindFragment into @objectID, @result

    END

    close FindFragment

    deallocate FindFragment

    drop table #IndexFrag

    Thats the full script, works well for what I need, but changing it to '[' + name + ']' brings back the error

    ''The data types varchar and ntext are incompatible in the add operator.''

    We can't do a select as in this scenario can we?

  • CREATE TABLE #IndexFrag(

    database_id int,

    object_ID int,

    index_id int,

    name varchar(max),

    page_count int,

    avg_fragmentation_In_Percent real )

    Change your NAME column to varchar(max) and I think your code will work.

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • First, see the notes above about Ola Hallengren's work. Second, you could also use the QUOTENAME function like this:

    select QUOTENAME(name) from sys.indexes;

    Another thing to keep in mind is that if you fire an index REBUILD, the index isn't available while the rebuild is running. If you're using Enterprise Edition, you can specify that you want it kept online by using the ONLINE option, but there are some restrictions you have to step around. See http://technet.microsoft.com/en-us/library/ms188388.aspx, which I'm sure you have already seen if you're writing this process.

  • So I had a little time to play with this script. I found where you introduced a bug when you added the square braces around your table name, where by you would never the table name on the following statement:

    WHERE i.name = @Result

    However by doing a compare on object_id should be sufficient so I changed your where clause to read"

    WHERE i.object_id = @objectID

    Also, I'm no big fan on looking for nulls the way you described in this construct:

    and (name not like 'null')

    When I look for nulls (or not null) I would use the following construct:

    and name not null

    So there is my 2 cents worth. These types of scripts teach you a lot about SQL Server. If you are fluent with the system tables and can spin up code rather quickly, then by all means roll your own. However there are a great number of resources where fellow DBAs have plowed that road before you and you can get something up and fully functional with little to no effort. So in other words, no need to reinvent the wheel as a number of others have pointed out before.

    Kurt

    Kurt W. Zimmerman
    SR DBA
    Lefrak Organization
    New York, NY

    http://www.linkedin.com/in/kurtwzimmerman

  • FYI: the wheel's been re-invented countless times, or we'd all be riding around on wooden wheels with no rims!

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Appreciate your feedback guys, I will make the changes this afternoon.

    Thanks for taking the time to test it Kurt, appreciate it and I prefer your way around nulls too.

    I appreciate it has been done before, but instead of implementing something somebody has already done, I saw this as a good way to learn.

  • Kurt, Making those adjustments still came back with the same error - very strange I can't see where it is getting it from.

    Ed, "using select QUOTENAME(name) from sys.indexes" worked a treat and seems to be coming back with the right results!

    Appreciate your help!

  • MysteryJimbo (12/9/2013)


    If you are developing an in-house index degfragmentation tool, I personally wouldn't invest the time reinventing the wheel. There are several options already developed and freely available on the web.

    ????? ??? ?? ?????? ??? ?????? ???? ????? ???????? ???? ????? ??????????? ?????? ??????? ???????

    We use this one in house, courtesy of Ola Hallengren.

    http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

    great, thanks Mystery for sharing the link

    شركات نظافة بالرياض[/url] تخزين اثاث[/url] تنظيف فلل[/url] شركة تنظيف منازل[/url] شركات تنظيف بالرياض[/url]

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply