August 26, 2010 at 8:01 am
Hi All,
I am probably having a bad day but I can not seem to get this seemingly simple TSQL to work. I am trying to use a simplified Index task based upon the fragmentation level but the Execute Sp_executeSql stmt does not run the sql. The printed version looks right but it does not run against the db (verified via profiler). Any ideas?
Thanks in advance re:
--Remove temp table if exists
IF OBJECT_ID(N'TEMPDB..#IX_TASK') IS NOT NULL
--SELECT OBJECT_ID(N'TEMPDB..#IX_TASK')
BEGIN
PRINT 'DROPPING TEMP TABLE'
DROP TABLE #IX_TASK
END
;
--Build IX list with fragmentation
WITH IX_Recommendation AS
(select (dense_rank() over (order by t4.name,t3.name))%2 as l1
, (dense_rank() over (order by t4.name,t3.name,t2.name))%2 as l2
, (dense_rank() over (order by t4.name,t3.name,t2.name,partition_number))%2 as l3
,t4.name as [schema_name]
, t3.name as table_name
, t2.name as index_name
,t1.object_id
,t1.index_id
,t1.partition_number
,t1.index_type_desc
,t1.index_depth
,t1.avg_fragmentation_in_percent
,t1.fragment_count
,t1.avg_fragment_size_in_pages
,t1.page_count
,CASE
WHEN t1.avg_fragmentation_in_percent between 5 and 30 then 'ReOrganize'
WHEN t1.avg_fragmentation_in_percent between 31 and 100 then 'ReBuild'
ELSE convert(varchar(10),t1.avg_fragmentation_in_percent)
END AS [Recommend]
from sys.dm_db_index_physical_stats(db_id(),NULL,NULL,NULL,'LIMITED' ) t1
inner join sys.objects t3 on (t1.object_id = t3.object_id)
inner join sys.schemas t4 on (t3.schema_id = t4.schema_id)
inner join sys.indexes t2 on (t1.object_id = t2.object_id and t1.index_id = t2.index_id )
where index_type_desc <> 'HEAP'
AND t1.avg_fragmentation_in_percent between 5 and 100
)
,
--Build statement list
STMTS AS (
SELECT [schema_name]+'.'+ table_name+' '[TableName]
,recommend [recommend]
,index_name [IX_Name]
FROM ix_recommendation
)
--SELECT * from stmts
--SELECT * FROM ix_recommendation --order by avg_fragmentation_in_percent desc ,partition_number
--Populate temp table for which to loop through
select * INTO #IX_TASK from stmts
ALTER TABLE #IX_TASK ADD [ID] INT identity(1,1)
--SELECT * FROM #IX_TASK
--Build loop
DECLARE @Stmt Nvarchar(500), @Tbls NVARCHAR(500),@IX_Name NVARCHAR(200),@Do nvarchar(20), @CRow INT, @MaxRow INT
SET @CRow=1
SELECT @MaxRow=count(ID) FROM #IX_TASK
WHILE @CRow<=@MaxRow
BEGIN
SELECT @Tbls=TableName
, @do=recommend
,@IX_Name=IX_Name
FROM #IX_TASK WHERE ID=@CRow
--Set statement for IX task
SET @STMT='ALTER INDEX '
SET @STMT=@STMT + @IX_Name + ' ON ' + @Tbls + '' + @Do + ';'
Print @stmt
EXECUTE SP_EXECUTESQL @STMT
SET @CRow=@CRow+1
END
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
August 26, 2010 at 10:48 am
You will need to put square brackets around your index and table names
August 26, 2010 at 10:52 am
Ness (8/26/2010)
...Execute Sp_executeSql stmt does not run the sql. The printed version looks right but it does not run against the db (verified via profiler). Any ideas?...
What it returns to you? Error?
August 26, 2010 at 11:04 am
BTW, I've tried your script - it works for me.
August 27, 2010 at 5:54 pm
Many thanks for your responses - I am not in the office for a while but I will check as soon as I return.
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply