Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Select from table and alter result


Select from table and alter result

Author
Message
SQLAssAS
SQLAssAS
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 1146
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.
MysteryJimbo
MysteryJimbo
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1348 Visits: 15327
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
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47165 Visits: 44346
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


SQLAssAS
SQLAssAS
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 1146
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?
Kurt W. Zimmerman
Kurt W. Zimmerman
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1062 Visits: 1396

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
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10210 Visits: 9539
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.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Kurt W. Zimmerman
Kurt W. Zimmerman
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1062 Visits: 1396
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
ScottPletcher
ScottPletcher
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3937 Visits: 6663
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)

Prosecutor James Blackburn, in closing argument in the "Fatal Vision" murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."
SQLAssAS
SQLAssAS
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 1146
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.
SQLAssAS
SQLAssAS
SSC-Addicted
SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)SSC-Addicted (418 reputation)

Group: General Forum Members
Points: 418 Visits: 1146
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search