locked
Problem Altering a Column from char(1) to char(3) RRS feed

  • Question

  •  

    Hi,

    I am using SQL Server 2000 and working on Query Analyzer.
    I want to write a script to change a data type of a column from char(1) to char(3) and here is what i am trying to execute:

     

    ALTER TABLE MEMBER_DETAIL
    ALTER COLUMN [MEMBER_TYPE] char(3) null


    And here is the Error:

    Server: Msg 5074, Level 16, State 8, Line 1
    The statistics 'MEMBER_TYPE' is dependent on column 'MEMBER_TYPE'.

    Server: Msg 4922, Level 16, State 1, Line 1
    ALTER TABLE ALTER COLUMN MEMBER_TYPE failed because one or more objects access this column.

     

    The same command works for STAGING Table

    ALTER TABLE MEMBER_DETAIL_STG
    ALTER COLUMN [MEMBER_TYPE] char(3) null

     

    What does it mean by referenced in other object? There are a bunch of Stored Procedures/Function in my application which may be referencing this table and this column in particular,does this mean it is being referenced..??

    What may be the other objects which are referencing..??

     

    And other thing i just noticed is that the column has data in it i.e there are 50% of records in this table have data 'F' or 'S'.
    Do you think this might be causing me the problem of not allowing me to change the datatype from char(1) to char(3)?

    Any suggestion/advice would be appreciated.

     

    thanks
    R

    Tuesday, September 9, 2008 1:56 PM

Answers

  • According to the error message, you have statistics created on the column, in which case you would need to drop the existing statistics first:

     

    http://msdn.microsoft.com/en-us/library/ms175075.aspx

     

    Secondly, you could run sp_depends Member_Detail to get a list of all items which depend on the table, from there you could see what additional items may/maynot be causing the error.

     

    http://msdn.microsoft.com/en-us/library/aa933275(SQL.80).aspx

    Tuesday, September 9, 2008 2:11 PM
  • Use sp_helpstats or inquiry sys.stats to see the statistics created by users on that table / column. Drop those statistics, could be also indexes, alter the table and then recreate the statistics or indexes using that colum. To see indexes use sp_helpindex or inquiry sys.indexes.

     

    Example:

     

    Code Snippet

    USE [tempdb]

    GO

     

    CREATE TABLE t1 (

    c1 CHAR(1) NULL

    )

    GO

     

    INSERT INTO t1(c1) VALUES('1')

    GO

     

    CREATE STATISTICS st_t1_c1 ON t1(c1) WITH fullscan

    GO

     

    EXEC sp_helpstats 't1'

    GO

     

    SELECT

    *

    FROM

    sys.stats AS a

    INNER JOIN

    sys.stats_columns AS b

    ON a.[object_id] = b.[object_id] AND a.stats_id = b.stats_id

    WHERE

    a.[object_id] = OBJECT_ID('t1')

    AND a.user_created = 1

    AND COL_NAME(b.[object_id], b.column_id) = 'c1'

    GO

     

    DROP STATISTICS t1.st_t1_c1

    GO

     

    ALTER TABLE [t1]

    ALTER COLUMN c1 CHAR(3) NULL

    GO

     

    CREATE STATISTICS st_t1_c1 ON t1(c1) WITH fullscan

    GO

     

    DROP TABLE t1

    GO

     

     

     

    AMB
    Tuesday, September 9, 2008 2:13 PM

All replies

  • According to the error message, you have statistics created on the column, in which case you would need to drop the existing statistics first:

     

    http://msdn.microsoft.com/en-us/library/ms175075.aspx

     

    Secondly, you could run sp_depends Member_Detail to get a list of all items which depend on the table, from there you could see what additional items may/maynot be causing the error.

     

    http://msdn.microsoft.com/en-us/library/aa933275(SQL.80).aspx

    Tuesday, September 9, 2008 2:11 PM
  • Use sp_helpstats or inquiry sys.stats to see the statistics created by users on that table / column. Drop those statistics, could be also indexes, alter the table and then recreate the statistics or indexes using that colum. To see indexes use sp_helpindex or inquiry sys.indexes.

     

    Example:

     

    Code Snippet

    USE [tempdb]

    GO

     

    CREATE TABLE t1 (

    c1 CHAR(1) NULL

    )

    GO

     

    INSERT INTO t1(c1) VALUES('1')

    GO

     

    CREATE STATISTICS st_t1_c1 ON t1(c1) WITH fullscan

    GO

     

    EXEC sp_helpstats 't1'

    GO

     

    SELECT

    *

    FROM

    sys.stats AS a

    INNER JOIN

    sys.stats_columns AS b

    ON a.[object_id] = b.[object_id] AND a.stats_id = b.stats_id

    WHERE

    a.[object_id] = OBJECT_ID('t1')

    AND a.user_created = 1

    AND COL_NAME(b.[object_id], b.column_id) = 'c1'

    GO

     

    DROP STATISTICS t1.st_t1_c1

    GO

     

    ALTER TABLE [t1]

    ALTER COLUMN c1 CHAR(3) NULL

    GO

     

    CREATE STATISTICS st_t1_c1 ON t1(c1) WITH fullscan

    GO

     

    DROP TABLE t1

    GO

     

     

     

    AMB
    Tuesday, September 9, 2008 2:13 PM
  • Hi:

    Thanks a lot Jay Bonk , hunchback &  Mangal9i.

    Appreciate your suggestions.

     

    All of your replies helped me , i dropped the statistics on that column made the modification and recreated the statistics with the same name.

     

     

    Thanks,

    R

    Tuesday, September 9, 2008 5:19 PM