September 30, 2011 at 6:49 am
Hi Guys
Please help
I have the following code which when executed generates a script to Rebuild Indexes and update stats based on the Where clauses.
How would I get the results to execute automatically without me having to copy and paste the results in a new query window.
select distinct 'ALTER INDEX ALL ON '+'['+TABLE_SCHEMA+'].'+'['+TABLE_NAME+']
REBUILD WITH (ONLINE = ON)
GO
IF @@ERROR = 0
PRINT '+'''['+TABLE_SCHEMA+'].'+'['+TABLE_NAME+'] REBUILD INDEX DONE!!!''
GO
UPDATE STATISTICS '+'['+TABLE_SCHEMA+'].'+'['+TABLE_NAME+']
WITH FULLSCAN
GO
IF @@ERROR = 0
PRINT '+'''['+TABLE_SCHEMA+'].'+'['+TABLE_NAME+'] UPDATE STATS DONE!!!''
GO'
from INFORMATION_SCHEMA.COLUMNS IC
inner join sysobjects SO
on IC.TABLE_NAME = SO.name
WHERE SO.type = 'U'
and '['+TABLE_SCHEMA+'].'+'['+TABLE_NAME+']' not in
(SELECT
DISTINCT '['+ic.TABLE_SCHEMA+'].'+'['+ic.[Table_Name]+']'
FROM
INFORMATION_SCHEMA.COLUMNS ic
Inner Join sys.sysobjects so
ON ic.[Table_Name] = so.[name]
Inner Join sys.sysindexes si
ON so.[id] = si.[id]
WHERE
ic.[DATA_TYPE] IN('text', 'ntext', 'image', 'xml')
OR [CHARACTER_MAXIMUM_LENGTH] = '-1'
)
and '['+TABLE_SCHEMA+'].'+'['+TABLE_NAME+']' in
(SELECT DISTINCT '['+SCHEMA_NAME(SO.schema_id)+'].'+'['+OBJECT_NAME(SO.object_id)+']'
FROM sys.indexes SI
INNER JOIN sys.objects SO
ON SO.object_id = SI.object_id
INNER JOIN sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, N'LIMITED')IPS
ON IPS.OBJECT_ID = SI.object_id
AND IPS.index_id = SI.index_id
where SI.name is not null
and IPS.avg_fragmentation_in_percent > 10
and SO.type = 'U'
)
Thanks
September 30, 2011 at 7:38 am
derekr 43208 (9/30/2011)
How would I get the results to execute automatically without me having to copy and paste the results in a new query window.
Untested, but something like this would work.
IF object_id('TempDB..#tempTable') IS NULL
BEGIN
DROP TABLE #tempTable
END
CREATE TABLE #tempTable (ID INT IDENTITY, [SQL] VARCHAR(4000))
INSERT INTO #tempTable
SELECT DISTINCT 'ALTER INDEX ALL ON ' + '[' + TABLE_SCHEMA + '].' + '[' + TABLE_NAME + ']
REBUILD WITH (ONLINE = ON)
GO
IF @@ERROR = 0
PRINT ' + '''[' + TABLE_SCHEMA + '].' + '[' + TABLE_NAME + '] REBUILD INDEX DONE!!!''
GO
UPDATE STATISTICS ' + '[' + TABLE_SCHEMA + '].' + '[' + TABLE_NAME + ']
WITH FULLSCAN
GO
IF @@ERROR = 0
PRINT ' + '''[' + TABLE_SCHEMA + '].' + '[' + TABLE_NAME + '] UPDATE STATS DONE!!!''
GO' AS [SQL]
FROM INFORMATION_SCHEMA.COLUMNS IC
INNER JOIN sysobjects SO ON IC.TABLE_NAME = SO.NAME
WHERE SO.type = 'U'
AND '[' + TABLE_SCHEMA + '].' + '[' + TABLE_NAME + ']' NOT IN (
SELECT DISTINCT '[' + ic.TABLE_SCHEMA + '].' + '[' + ic.[Table_Name] + ']'
FROM INFORMATION_SCHEMA.COLUMNS ic
INNER JOIN sys.sysobjects so ON ic.[Table_Name] = so.[name]
INNER JOIN sys.sysindexes si ON so.[id] = si.[id]
WHERE ic.[DATA_TYPE] IN ('text','ntext','image','xml')
OR [CHARACTER_MAXIMUM_LENGTH] = '-1'
)
AND '[' + TABLE_SCHEMA + '].' + '[' + TABLE_NAME + ']' IN (
SELECT DISTINCT '[' + SCHEMA_NAME(SO.schema_id) + '].' + '[' + OBJECT_NAME(SO.object_id) + ']'
FROM sys.indexes SI
INNER JOIN sys.objects SO ON SO.object_id = SI.object_id
INNER JOIN sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, N'LIMITED') IPS ON IPS.OBJECT_ID = SI.object_id
AND IPS.index_id = SI.index_id
WHERE SI.NAME IS NOT NULL
AND IPS.avg_fragmentation_in_percent > 10
AND SO.type = 'U'
)
DECLARE @sql VARCHAR(4000), @COUNTER INT
SELECT @COUNTER = COUNT(*)
FROM #tempTable
WHILE @COUNTER > 0
BEGIN
SELECT @sql = [SQL]
FROM #tempTable
WHERE ID = @COUNTER
EXECUTE (@SQL)
DELETE
FROM #tempTable
WHERE ID = @COUNTER
SELECT @COUNTER = COUNT(*)
FROM #tempTable
END
September 30, 2011 at 11:29 am
You don't need to know the exact number of rows in the temp table to know you need to continue. Knowing there is at least one row is sufficient. So you better code that loop as this:
DECLARE @sql VARCHAR(4000);
DECLARE @ID int;
WHILE exists (
select top 1 *
from #tempTable
)
BEGIN
SELECT top 1
@ID = ID,
@sql = [SQL]
FROM #tempTable;
EXECUTE (@SQL);
DELETE
FROM #tempTable
WHERE ID = @ID;
END
or maybe even:
DECLARE @sql VARCHAR(4000);
DECLARE @ID int;
WHILE 1 = 1
BEGIN
SELECT top 1
@ID = ID,
@sql = [SQL]
FROM #tempTable;
if not @@rowcount > 0
break;
EXECUTE (@SQL);
DELETE
FROM #tempTable
WHERE ID = @ID;
END
September 30, 2011 at 11:53 am
If you use FOR XML PATH('') to create your SQL command string, it will create a single string rather than one per index, so you don't even need the loop. You would have to be careful about entitization, but that's fairly simple to account for.
Also, you should be using Quotename() for your object names. If someone creates an object containing ']', which is a bad idea of course, your script will break.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 3, 2011 at 5:55 am
Thanks tested but doesnt work
This is the result:
(18 row(s) affected)
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'GO'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'GO'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'GO'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near 'GO'.
October 3, 2011 at 6:07 am
October 3, 2011 at 6:29 am
derekr 43208 (10/3/2011)
Thanks tested but doesnt workThis is the result:
(18 row(s) affected)
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'GO'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'GO'.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near 'GO'.
Msg 102, Level 15, State 1, Line 12
Incorrect syntax near 'GO'.
You'll have to leave the 'go' off if you try to execute all commands in a single batch using sp_executesql. Use ';' to separate the commands instead.
October 3, 2011 at 6:34 am
Thanks all
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply