SQL Server Performance Forum – Threads Archive

Bulk change of data types in Schema

<br />We’re importing a large legacy database off Unix. A number of columns are being incorrectly translated to ‘bit’ datatypes. Is there a way to globally change all ‘bit’ datatype columns to another type (char or nvarchar)? I thought it might be possible to change the ‘bit’ datatype (syscolumns.xtype=104) to nvarchar(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> (syscolumns.xtype=167 and prec=<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> but was concerned that I might miss updating some other column. There about 300 tables and it would be tedious to do it manually. Has anyone changed the datatypes through systables before?<br /><br />Thanks<br />Eliza

---

USE Northwind
GO CREATE TABLE myTable99(Col1 bit)
GO sp_Help myTable99
GO DECLARE myCursor99 CURSOR
FOR
SELECT ‘ALTER TABLE ‘ + TABLE_NAME + ‘ ALTER COLUMN ‘+ COLUMN_NAME + ‘ varchar(10) ‘
FROM INFORMATION_SCHEMA.Columns
WHERE DATA_TYPE = ‘bit’
AND TABLE_NAME = ‘myTable99’ OPEN myCursor99 DECLARE @SQL varchar(8000) FETCH NEXT FROM myCursor99 INTO @SQL WHILE @@FETCH_STATUS = 0
BEGIN
EXEC(@SQL)
FETCH NEXT FROM myCursor99 INTO @SQL
END CLOSE myCursor99
DEALLOCATE myCursor99
GO sp_Help myTable99
GO DROP TABLE myTable99
GO Brett

---

]]>