Problem with script

[opus06]

What is wrong with below script

select 'EXEC sp_dropextendedproperty

@name = ''MS_Description''

,@level0type = ''schema''

,@level0name = ' + object_schema_name(extended_properties.major_id) + '

,@level1type = ''table''

,@level1name = ' + object_name(extended_properties.major_id)

from sys.extended_properties

where extended_properties.class_desc = 'OBJECT_OR_COLUMN'

and extended_properties.minor_id = 0

and extended_properties.name = 'MS_Description'

It is giving me
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "extended_properties.class_desc" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "extended_properties.minor_id" could not be bound.

Thanks in advance

 

[VintageWine]

Suggest look at inverted commas. The single inverted command between "select" and "EXEC" looks suspect.

 

[bborissov]

Try:

[COLOR=blue]select[/color] [COLOR=red]'EXEC sp_dropextendedproperty
[/color]             @name = [COLOR=red]''[/color]MS_Description[COLOR=red]''[/color]
            ,@level0type = [COLOR=red]''[/color][COLOR=blue]schema[/color][COLOR=red]''[/color]
            ,@level0name = [COLOR=red]''' + object_schema_name(extended_properties.major_id) + '''[/color]
            ,@level1type = [COLOR=red]''[/color][COLOR=blue]table[/color][COLOR=red]''[/color]
            ,@level1name = [COLOR=red]''' + object_name(extended_properties.major_id)+''''
[/color][COLOR=blue]from[/color] sys.extended_properties
[COLOR=blue]where[/color] extended_properties.class_desc = [COLOR=red]'OBJECT_OR_COLUMN'[/color]
and extended_properties.minor_id = 0
and extended_properties.name = [COLOR=red]'MS_Description'[/color]

As you see you missed some quotes here and there

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.