DBCC SHOWCONTIG Problem

Question

DBCC SHOWCONTIG Problem

kemo

Old Hand

Points: 326
More actions
April 12, 2006 at 8:56 am

#90998

Can someone please help me with a Stored Procedure problem.
One of our stored procedures is failing because one of the tables in database has quotes in its name, ie.'FAC treaties$'
I've pulled out the bit of code that is failing...
EXEC ('DBCC SHOWCONTIG (''[' + @TableSchema +'].['+ @TableName + ']'') WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS')
I tried the following but can't get this to work.
set quoted_identifier on
EXEC ('DBCC SHOWCONTIG ("'FAC treaties$'") WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS')
EXEC ('DBCC SHOWCONTIG (['FAC treaties$']) WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS')
set quoted_identifier off
EXEC ('DBCC SHOWCONTIG ("'FAC treaties$'") WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS')
EXEC ('DBCC SHOWCONTIG (['FAC treaties$']) WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS')
The error is :
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'FAC'.

The DBCC command on its own without being inside EXEC works fine either way :
DBCC SHOWCONTIG ("'FAC treaties$'") WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS
DBCC SHOWCONTIG (['FAC treaties$']) WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS

Any ideas ? Please

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

sushila SSC-Dedicated Points: 35293 More actions · Answer 1

Try using dynamic sql:

DECLARE @sql VarChar(100)
DECLARE @tblName VarChar(15)
SET @tblName = '["FAC Treaties$"]'
SET @sql = 'DBCC SHOWCONTIG (' + @tblName + ') WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS'
EXEC(@sql)

why does the db have tables with single quotes in their names ?!?!

Could you please share the reasoning behind this naming convention ?!

**ASCII stupid question, get a stupid ANSI !!!**

kemo Old Hand Points: 326 More actions · Answer 2

Thanks for your reply Sushila,

I tried your sugggestions two ways but can't get it to work (details below).

As for the naming convention. Unfortunately the tables are created within an application and we have no control over what they call the objects. so we end up with spaces, single quotes, etc

Its only when our stored procedures fail and I delve in deep that I pick it up

set quoted_identifier off

DECLARE @sql VarChar(100)

DECLARE @tblName VarChar(15)

SET @tblName = "'FAC Treaties$'"

SET @sql = 'DBCC SHOWCONTIG (' + @tblName + ') WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS'

EXEC(@sql)

Server: Msg 2501, Level 16, State 45, Line 1

Could not find a table or object named 'FAC Treaties$'. Check sysobjects.

set quoted_identifier on

DECLARE @sql VarChar(100)

DECLARE @tblName VarChar(15)

SET @tblName = "'FAC Treaties$'"

SET @sql = 'DBCC SHOWCONTIG (' + @tblName + ') WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS'

EXEC(@sql)

Server: Msg 207, Level 16, State 3, Line 4

Invalid column name ''FAC Treaties$''.

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

How about using the tblName variable the way it is in my example and then running it - what does that get you ?!?!

SET @tblName = '["FAC Treaties$"]'

**ASCII stupid question, get a stupid ANSI !!!**

Fred Williams-134192 SSCrazy Points: 2482 More actions · Answer 4

I will bet 25c on:

EXEC ('DBCC SHOWCONTIG ([''FAC treaties$'']) WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS')

(those are two single quotes, not double quotes)

kemo Old Hand Points: 326 More actions · Answer 5

yeah I did that (forgot to copy that in my earlier reply). I got a whole bunch of other errors :

Server: Msg 105, Level 15, State 1, Line 1

Unclosed quotation mark before the character string '"FAC Treaties$) WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS'.

Server: Msg 170, Level 15, State 1, Line 1

Line 1: Incorrect syntax near '"FAC Treaties$) WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS'.

I also tried SET @tblName = ['FAC Treaties$']

and SET @tblName = ["FAC Treaties$"]

These gave the same errors as SET @tblName = "'FAC Treaties$'"

GaryMcAllister Mr or Mrs. 500 Points: 521 More actions · Answer 6

Try this :-

DECLARE

@sSql AS NVARCHAR(4000)

DECLARE

@StupidTableName AS NVARCHAR(1024)

SELECT

@StupidTableName = '''StupidTableName$'''

SELECT

@sSql = 'DBCC SHOWCONTIG (' + char(91) + @StupidTableName + char(93) + ')'

EXEC

(@sSql)

GaryMcAllister Mr or Mrs. 500 Points: 521 More actions · Answer 7

GaryMcAllister

Mr or Mrs. 500

Points: 521

April 13, 2006 at 8:04 am

#632391

Do I win ?

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 8

Only if the solution uses sp_MSforeachtable ... LOL !

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

kemo Old Hand Points: 326 More actions · Answer 9

Yes I got it to work for that stupid named table. The problem now is this is selecting tables within a cursor and not all of them have stupid names.

PS I used this in the end...

select @sSQL = 'DBCC SHOWCONTIG (['+ char(39) + @mytab + char(39) + ']) WITH ALL_INDEXES, TABLERESULTS, NO_INFOMSGS'

Thanks for all your help

GaryMcAllister Mr or Mrs. 500 Points: 521 More actions · Answer 10

Thats why I used char(91) and char(93) it wouldn't matter what the table name was then.

kemo Old Hand Points: 326 More actions · Answer 11

I did try the [ ] but still got errors..I'll see if I can work it out

Thanks