Help (DBCC SHOWCONTIG ( + @tablename + )

Hi everyone, I'm SO glad I found this site.
Ok, here's my problem...

I found this stored proc somewhere to help defrag some tables, well this one line is giving me the hardest time. First I was getting an error about using FAST ('FAST' is not a recognized option.), so I took that out.
Now I'm getting 'ALL_INDEXES' is not a recognized option.

Here's the line giving me the problems.
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''') with TABLERESULTS, NO_INFOMSGS,ALL_INDEXES')

Any ideas?

Thank you!RE: Here's the line giving me the problems.
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''') with TABLERESULTS, NO_INFOMSGS,ALL_INDEXES')

Note: Often the line where the error is 'reported' to be (in query analyzer, etc.) is not where it the issue actually is; frequently that is just where the query parser 'chokes' on it. The line you gave in isolation should work OK. Have you tried it seperatly to test it? For example try:

Use Pubs
GO

Declare @.tablename As NVarChar (256)
Select @.tablename = 'Authors'

Exec ('DBCC SHOWCONTIG (''' + @.tablename + ''') with TABLERESULTS, NO_INFOMSGS, ALL_INDEXES')|||That keyword is not valid. If you include the table id without the index id - all indexes will be processed.|||RE: That keyword is not valid. If you include the table id without the index id - all indexes will be processed.

I'm not sure I understand what you mean, if you mean All_Indexes; as far as I understand it will cause results for "all indexes" to be displayed. In this case for Pubs..authors?

Use Pubs
GO

Declare @.tablename As NVarChar (256)
Select @.tablename = 'Authors'

Exec ('DBCC SHOWCONTIG (''' + @.tablename + ''') with TABLERESULTS, NO_INFOMSGS, ALL_INDEXES')|||I needed to be more specific - ALL_INDEXES is not a valid keyword for sql server 7 - that is why criki is having the problem. Same reason why fast was not working.|||Originally posted by rnealejr
I needed to be more specific - ALL_INDEXES is not a valid keyword for sql server 7 - that is why criki is having the problem. Same reason why fast was not working.

I had assumed criki was on Sql Server 2k; also I thought Sql 2k returns esults for a clustered / pk index (if you leave out index ids...) I guess 7.0 returns results for all inexes if you leave out index ids?|||Whoa! You guys lost me.
I am on 2000. I found the code in help. I would assume FAST and ALL_INDEXES would be accurate. I just figured there's some setting I'm missing.

The full code is in SQL Server Books Online under DBCC SHOWCONTIG|||You say you are running SQL Server 2000, was this an install or an upgrade from SQL Server 7.0. If it was an upgrade your Compatibility Level for the database may still be set to SQL Server 7.

Run this to see the levels
select cmptlevel,name from master.dbo.sysdatabases
If a database comes back with a cmptlevel not equal to 80, then it is not at 2000 level.

You can set a databases Compatibility Level by running sp_dbcmptlevel. However this may have some effect on your stored procedures, so test.|||They're all 70. :-(
Most of my databases don't have stored procedures, luckily in this case.

What type of issues and is there anyway to determine this before running running sp_dbcmptlevel?|||The only difference between levels 70 and 80 is that several reserved keywords introduced in SQL Server 2000 are not supported in level 70.|||When you upgrade from 7 to 2000 the default compatibility is to 2000 (8) so I am surprised that your compatibility it still for 7. Anyway, the short answer to your question is yes. Check out the following ms articles:

ms article 1 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_backcomp_02wj.asp)

ms article 2 (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/instsql/in_backcomp_5dx1.asp) - This one says version 6.5, but there is some important information in general and specifically to version 7.|||So sorry! I was told Sql 2K but it is in fact 7.0. (that's what I get for not checking myself)

Now, I guess, best thing to do is just rewrite the proc to include the correct fields in the table creation and insert.

I'll have to start a new thread!

Thanks!