January 20, 2012 at 11:52 am
I'm having a hard time with this script in SQL Server 2000:
USE Prototype
GO
DECLARE @rc int;
DECLARE @mycommand1 nvarchar(2000);
DECLARE @mycommand2 nvarchar(2000);
SET @mycommand1 = N'DBCC DBREINDEX(?)';
-- SET @mycommand2 = N'DBCC INDEXDEFRAG(0, ?)';
-- EXEC @rc = master.dbo.sp_MSforeachtable @command1 = @mycommand1, @command2 = @mycommand2;
EXEC @rc = master.dbo.sp_MSforeachtable @command1 = @mycommand1;
SELECT @rc AS ReturnCode;
Whether I use the commented code or not, it always returns immediately with 0 as the ReturnCode. I've also tried this syntax with the same result:
USE Prototype
GO
DECLARE @rc int;
DECLARE @mycommand1 nvarchar(2000);
DECLARE @mycommand2 nvarchar(2000);
SET @mycommand1 = N'DBCC DBREINDEX(''?'')';
-- SET @mycommand2 = N'DBCC INDEXDEFRAG(0, ''?'')';
-- EXEC @rc = master.dbo.sp_MSforeachtable @command1 = @mycommand1, @command2 = @mycommand2;
EXEC @rc = master.dbo.sp_MSforeachtable @command1 = @mycommand1;
SELECT @rc AS ReturnCode;
I'm experimenting with moving a lot of data to a new database for archiving and I want the source database as compact as possible after the move. I realize the syntax for INDEXDEFRAG is wrong and if anyone has any ideas on how to do that too I would appreciate it! Thanks!
January 20, 2012 at 1:13 pm
do you need the results on a per table command?
i've done something similar by building the string and executing it:
DECLARE @mycommand1 nvarchar(max);
SET @mycommand1=''
SELECT @mycommand1 = @mycommand1
+ N'DBCC DBREINDEX('''
+ QUOTENAME(schema_name(schema_id))
+ '.'
+ QUOTENAME(name)
+ ''');'
+ CHAR(13)
+ CHAR(10)
FROM sys.tables
PRINT @mycommand1
--EXEC(@mycommand1)
Lowell
January 20, 2012 at 1:49 pm
@lowell - what is the value of "max"? In SQL Server 2000 I get an "Incorrect syntax near (max)" error.
ETA: I'm also getting this error - "'schema_name' is not a recognized function name."
Sorry I guess I should have said I was using SQLServer 2000 in my original post. I will change it now!
January 20, 2012 at 2:02 pm
ahh didn't know you were in SQL 2000; didn't read the forum.
max has no practical limit, but is SQL2005 and above, sorry.
maxsize in SQL2000 is NVARCHAR(4000) for an NVARCHAR; and assuming each table is say, 20 characters or less, the stack of commands averages 42 chars per table, so from my example will stop working at around 100 tables; the rest won't fit in the string i was building
sp_msforEachTable or an explicit cursor would be better in this case.
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply