December 26, 2007 at 11:52 am
i have a job which is failing
my lead wants me to avoid the cursors in that job and modify it
any suggestion would be of great help please
--
DECLARE @Database VARCHAR(255)
DECLARE @Table VARCHAR(255)
DECLARE @cmd NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 90
DECLARE DatabaseCursor CURSOR FOR
SELECT database_name FROM WHERE status='y'
AND database_name NOT IN ('master','model','msdb','tempdb')
ORDER BY 1
OPEN DatabaseCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'DECLARE TableCursor CURSOR FOR SELECT table_catalog + ''.'' + table_schema + ''.'' + table_name as tableName
FROM ' + @Database + '.INFORMATION_SCHEMA.TABLES WHERE table_type = ''BASE TABLE'''
-- create table cursor
EXEC (@cmd)
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @Table
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = 'ALTER INDEX ALL ON ' + @Table + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@cmd)
FETCH NEXT FROM TableCursor INTO @Table
END
CLOSE TableCursor
DEALLOCATE TableCursor
FETCH NEXT FROM DatabaseCursor INTO @Database
END
CLOSE DatabaseCursor
DEALLOCATE DatabaseCursor
thanks
December 27, 2007 at 7:14 am
All that I have in my script library is a cursor-based script, which believe came from the SQL Server 2000 Books Online (BOL).
--
--Begin Script--
/*Perform a 'USE ' to select the database in which to run the script.*/
-- Declare variables
USE CG
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
-- Decide on the maximum fragmentation to allow
SELECT @maxfrag = 5.0
-- Declare cursor
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the table
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
-- Open the cursor
OPEN tables
-- Loop through all the tables in the database
FETCH NEXT
FROM tables
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT
FROM tables
INTO @tablename
END
-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables
-- Declare cursor for list of indexes to be defragged
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor
OPEN indexes
-- loop through the indexes
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ',
' + RTRIM(@indexid) + ')'
EXEC (@execstr)
FETCH NEXT
FROM indexes
INTO @tablename, @objectid, @indexid, @frag
END
-- Close and deallocate the cursor
CLOSE indexes
DEALLOCATE indexes
-- Delete the temporary table
DROP TABLE #fraglist
GO
--End Script--
Happy T-SQLing
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
December 27, 2007 at 8:31 am
Hey Damon
Thanks for your reply and i appreciate your time and i did liked your code anyways my lead wants me to remove all the cursors use something else instead of cursor's so i dont want any cursor's
Thanks
if you can provide a solution without any cursor would be a great help
December 27, 2007 at 9:09 am
You could do something like:
create table #tables (table_name varchar(80), completed datetime)
insert #tables
SELECT TABLE_NAME, null
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
while exists (select table_name from #tables where completed is null)
begin
select top 1 @table = table_name from #tables where completed is null
... exec code
update #tables
set completed = getdate()
where table_name = @table
end
Use this to drive your system. Not sure it's much better than a cursor, but if you change #tables to some permanent table, you can track when things are happening, even restart if the job fails with a new table.
December 28, 2007 at 1:20 am
We use acursor driven proc but just altering it to be #temptbl oriented, you might handle it like this:
DECLARE @SQLStatement nvarchar(4000)
--DECLARE TableList CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
Declare @tmpReindexList table (SeqNo int identity(1,1) not null, SQLStatement nvarchar(4000) not null)
insert into @tmpReindexList (SQLStatement)
SELECT
N'DBCC DBREINDEX (''['+ db_name() + '].' +
QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME)
+ ''')' as SQLStatement
FROM
INFORMATION_SCHEMA.TABLES
WHERE EXISTS
(
SELECT *
FROM sysindexes
WHERE id =
OBJECT_ID(QUOTENAME(TABLE_SCHEMA) +
N'.' +
QUOTENAME(TABLE_NAME))
AND
indid IN(0,1)
)
ORDER BY 1
-- OPEN TableList
declare @SeqNo int
WHILE 1 = 1
BEGIN
--FETCH NEXT FROM TableList INTO @TableName
Select top 1 @SeqNo = SeqNo, @SQLStatement=SQLStatement
from @tmpReindexList
if @@rowcount = 0 break
print @SQLStatement
EXEC(@SQLStatement)
delete from @tmpReindexList where SeqNo = @SeqNo
END
-- CLOSE TableList
-- DEALLOCATE TableList
exec sp_updatestats
dbcc updateusage(0) with count_rows
btw I've kept the commented lines in the code so you can easily see the difference :hehe:
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply