Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    4

    Unanswered: Loop through all tables and columns

    Is there a way to change the collation against all the tables and columns within a database?

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Easy way? No. Best way is probably to select from the information_schema views, and generate a set of alter table commands. Save that script, in case you need it again. I don't have the syntax on hand, but I think
    Code:
    alter table blah alter column yadda varchar(nnn) collate SQL_Latin1...
    In order to get the data type correct, you can disect the sp_help stored procedure to see how MS does it.

  3. #3
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up Re

    Quote Originally Posted by NathanFuller
    Is there a way to change the collation against all the tables and columns within a database?
    look this example query,This query will generate 'alter table' statement.u can specify ur filter condition in where clause for ur specfic requirment.Use this code by your own risk.

    SELECT
    'ALTER TABLE ' + TABLE_NAME +
    ' ALTER COLUMN ' + COLUMN_NAME +
    ' ' + DATA_TYPE +' '+
    CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NOT NULL
    THEN '('+(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH)+')' )
    ELSE isnull(CONVERT(VARCHAR,CHARACTER_MAXIMUM_LENGTH),' ')
    END
    +' COLLATE SQL_Latin1_General_CP850_BIN '+
    CASE IS_NULLABLE
    WHEN 'YES' THEN 'NULL'
    WHEN 'No' THEN 'NOT NULL'
    END

    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE DATA_TYPE IN ('varchar' ,'char','nvarchar','nchar')
    Last edited by mallier; 11-01-05 at 10:39.

Posting Permissions

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