Post reply

Problem with Tsql script

  • October 2, 2008 at 5:29 pm

    #213526

    What is wrong with below script

    select 'EXEC sp_addextendedproperty

    @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

  • October 3, 2008 at 1:05 am

    #880308

    That's because you need to qualify view with schema (owner in SQLS 2000) every time you mention its name... or use an alias.

    select 'EXEC sp_dropextendedproperty

    @name = ''MS_Description''

    ,@level0type = ''schema''

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

    ,@level1type = ''table''

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

    from sys.extended_properties

    where sys.extended_properties.class_desc = 'OBJECT_OR_COLUMN'

    and sys.extended_properties.minor_id = 0

    and sys.extended_properties.name = 'MS_Description'

    or

    select 'EXEC sp_dropextendedproperty

    @name = ''MS_Description''

    ,@level0type = ''schema''

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

    ,@level1type = ''table''

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

    from sys.extended_properties ep

    where ep.class_desc = 'OBJECT_OR_COLUMN'

    and ep.minor_id = 0

    and ep.name = 'MS_Description'

  • October 3, 2008 at 9:59 am

    #880562

    Thanks vladan it worked !! Now it gives me resultset of

    EXEC sp_dropextendedproperty @name = 'MS_Description' ,@level0type = 'schema' ,@level0name = dbo ,@level1type = 'table' ,@level1name = ''

    Can anyone tell me how to exe resultset without manually interaction

  • October 3, 2008 at 1:01 pm

    #880668

    Simple way is to use a cursor loop..

    DECLARE @sql nvarchar(512),

    @C cursor

    SET @C = CURSOR FOR

    SELECT 'EXEC sp_dropextendedproperty

    @name = ''MS_Description''

    ,@level0type = ''schema''

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

    ,@level1type = ''table''

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

    FROM sys.extended_properties

    WHERE sys.extended_properties.class_desc = 'OBJECT_OR_COLUMN'

    AND sys.extended_properties.minor_id = 0

    AND sys.extended_properties.name = 'MS_Description'

    OPEN @C

    FETCH @C INTO @sql

    WHILE @@FETCH_STATUS = 0

    BEGIN

    PRINT @sql -- Just to show what would actually run

    -- Un-comment the following line to run it dynamically

    -- EXEC sp_executesql @sql

    FETCH @C INTO @sql

    END

    CLOSE @C

    DEALLOCATE @C

    Gary Johnson
    Sr Database Engineer

  • October 3, 2008 at 1:46 pm

    #880687

    Thanks Gary it worked

  • October 3, 2008 at 7:27 pm

    #880768

    Heh... You're all destroying the ability to produce an automated data dictionary for a database and none of you even bat an eyelash at that. :blink: Sure, sure... this might be the final step prior to distributing the database so the customer doesn't get any wise ideas, but none of you even asked the question as to "Why" someone would want to destory some hard earned self contained documentation in the database. Absolutely amazing...

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 3, 2008 at 7:49 pm

    #880775

    Actually Jeff,

    I did think about that... And in fact our team is now doing a skunkworks project undertaking reading the dictionary from Erwin and putting it in the DB as we WANT to have the dictionary as part of our source code.

    But each to their own! 🙂

    Gary Johnson
    Sr Database Engineer

  • October 3, 2008 at 8:14 pm

    #880780

    Still, the deed is done and the OP is gonna whack the internal documentation in someone's DB. Questions like this should simply go unanswered. 😉

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

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