Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2008
    Posts
    12

    Unanswered: Alter Table Issue

    Hello,

    I am trying to alter a field in one of my tables to change the data type from int to varchar using the following:

    ALTER table 'MyTableName' ALTER column 'MyFieldName' varchar(50)

    When I run this in Query Analyzer, the following error is generated:

    "The statistics 'MyFieldName' is dependant on 'MyFieldName."
    "Alter table alter column 'MyFieldName' failed because one or more objects access this column."

    I can use Enterprise Manager to change the field manually, but I need to use the alter table statement.

    Can anyone help in what this error means?

    Thank You

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    As the error points out, there is an object (in this case a set of statistics) that references the column. You need to drop the statistics, then recreate them after the column is altered.

  3. #3
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    The column in question has an index against it because it was used in a query (system generated) or explicitly applied. Try this to find the name of the statistic:

    Code:
    USE database
    GO
    SELECT 'Index Name' = i.name, 
       'Statistics Date' = STATS_DATE(i.id, i.indid)
    FROM sysobjects o, sysindexes i
    WHERE o.name = '<tablename>' AND o.id = i.id
    GO
    then user the drop statistics command (see BOL)

    -- This is all just a Figment of my Imagination --

  4. #4
    Join Date
    Mar 2008
    Posts
    12
    Thank you, my field was found in the statement you provided.

    Im not too familiar in this area - since this field is found here, is there a way to tell if it was system generated or explicitly applied? Is there any way for me to tell which query this field was used in?

    Thanks

  5. #5
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Sure ... system generated statistics are preceded with an underscore ... H

    Here's an example in the code below.

    I chose the Northwind database.

    The first "SELECT 'Index Name'" query was run against the Customer table where only user created indexes had been applied to the table.

    Then I ran a query to select from the table based on a non-indexed column.

    The results of the second "SELECT 'Index Name'" query now shows a sisth index name & it was system generated.

    Code:
    USE database  Northwind                                        
    GO
    SELECT 'Index Name' = i.name, 
       'Statistics Date' = STATS_DATE(i.id, i.indid)
    FROM sysobjects o, sysindexes i
    WHERE o.name = 'Customers' AND o.id = i.id
    GO
     
    Results:
    Index Name                                            
    ------------------------------------------------------
    PK_Customers                                          
    City                                                  
    CompanyName                                           
    PostalCode                                            
    Region                                                
    (5 row(s) affected)
    
     
    select * from customers where ContactTitle = 'Owner'
     
    USE database  Northwind                                        
    GO
    SELECT 'Index Name' = i.name, 
       'Statistics Date' = STATS_DATE(i.id, i.indid)
    FROM sysobjects o, sysindexes i
    WHERE o.name = 'Customers' AND o.id = i.id
    GO
     
    Results:
    Index Name                                            
    ------------------------------------------------------
    PK_Customers                                          
    City                                                  
    CompanyName                                           
    PostalCode                                            
    Region                                                
    _WA_Sys_ContactTitle_7B905C75                         
    (6 row(s) affected)
    */

    -- This is all just a Figment of my Imagination --

  6. #6
    Join Date
    Mar 2008
    Posts
    12
    Thank you, sorry to keep asking questions....I think I am getting closer....
    The field in question seems to be a user created index.
    How would someone have defined\created this on this field?

  7. #7
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    CREATE INDEX <indexname> on <tablename> (<column1name>).

    Look up CREATE INDEX in BOL. If it's a primary key, look up ALTER TABLE in BOL.

    The easiest way to see this in action is to script out the table from Management Studio (SQL 2005) or Enterprise Managler ... err ... Manager(SQL 2000) and in the "Options" tab select script index, and Script Primary keys, Foreign Keys and the rest of the verbiage.

    It's good to ask questions to learn, but don’t be afraid to GOOGLE questions also ... there is a wealth of knowledge at your fingertips. Also, join a good forum or two (wink, wink ... nudge, nudge), read the posts, and post when you can help someone else.

    This is really a pay it forward sort of thing.

    Glad to help!

    -- This is all just a Figment of my Imagination --

  8. #8
    Join Date
    Mar 2008
    Posts
    12
    Thank you for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •