Post reply

remove all description

  • January 3, 2011 at 6:29 am

    #232570

    i have 2000 tables in my database.when i right click in table and click design table, i have from all of field column property and table designer and description . ok ? all of field and all of tables in database have dascription. ok?

    i want to delete all of description from all of field in table and all of 2000 tables .

    can i do it?

    please advise me?

    please help me?

    i need it very very soon.

    do u underestand me?

  • January 3, 2011 at 6:36 am

    #1268446

    Why is this so incredibly urgent?

    If I'm understanding you correctly, you're talking about extended properties. See it this returns the descriptions that you want to be rid of.

    SELECT * FROM sys.extended_properties

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 3, 2011 at 6:39 am

    #1268449

    putting in all those descriptions was incredibly time intensive for someone to do...and you want to delete them because they bother you, not because of a business reason.

    You should check with your technical writer, other developers or whoever else may have put that info in place before you even consider removing it.

    that's like deleting the payroll table because it "bothers" you...

    it will certainly have side effects you have not considered.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 3, 2011 at 6:45 am

    #1268454

    Lowell (1/3/2011)

    putting in all those descriptions was incredibly time intensive for someone to do...and you want to delete them because they bother you, not because of a business reason.

    I'm exceedingly curious why he wants to remove them all. No good reason usually for that and unless i missed it no reason given in initial post.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 3, 2011 at 7:42 am

    #1268470

    GilaMonster (1/3/2011)

    I'm exceedingly curious why he wants to remove them all. No good reason usually for that and unless i missed it no reason given in initial post.

    re-reading the post, i think he might be saying every description has the value "ok?", which might be annoying, I agree...if that were the case, then removing the onces that say "ok?" might make sense. I'll wait for the orginal poster to clarify what is really going on.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 3, 2011 at 11:10 pm

    #1268748

    wow !!! my manager told me : Mrs.ghorbani Please delete all description?

    because all table have idle description . please give me script for delete all of EXTENDED PROPERTY.

  • January 4, 2011 at 12:42 am

    #1268782

    i found this script for delete scripthion for only one field in a table. but i want to delete all description from all field from table not one by one i do this :

    exec sys.sp_dropextendedproperty

    please help me???

  • January 4, 2011 at 12:54 am

    #1268789

    That proc is what you use to delete extended properties (and is the only thing that you can use)

    Use the view I gave you earlier and you can either run a cursor over that and run the proc for each description, or use it to generate all the exec statements and then run the whole lot.

    Bear in mind that extended properties are used for other things than descriptions, so be careful what you delete. Recommend running on a test database first.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 4, 2011 at 2:50 am

    #1268823

    please give me the script of that cursor.please give me script . i dont know what do i do ? what do i write ?i didnt work with cursor?please help me?

  • January 4, 2011 at 3:35 am

    #1268836

    How much SQL experience do you have?

    The basic structure of a cursor is shown here:

    http://msdn.microsoft.com/en-us/library/ms180169%28v=SQL.100%29.aspx

    The first thing you need to do to get this write is to take the view that I mentioned earlier and write a query against that view that returns all the extended properties that you want to drop. Make sure that it only returns the ones that you want to drop.

    As I mentioned, you can use a cursor (which you're apparently not familiar with), or you can use the query to generate statements to drop the properties. Either will work fine, the cursor's probably a little harder to write but easier to understand.

    As an example of what I mean by 'generating statements', the following generates SELECT statements for all tables. You can then select the results of the query and run them:

    SELECT 'SELECT * FROM ' + name FROM sys.tables

    Which way would you prefer? Whichever way, first thing you need to do is write the query that returns all the properties that you want to drop.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 10 posts - 1 through 10 (of 10 total)

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