Changing data type to the fields of my tables.

Hello.

i have a database with 300 tables. All the data types of the fields of my tables are custom. Ex. IFGint:int and stuff like that.

I want to know how can i through a stored procedure, change in batch mode all the fields of my tables. I don't want to modify by hand everytable. It's a lot of work and i think that maybe there might be a way for this programatically.

thanks in advance

You can use SQL queries to generate the DDL statements that you need, something like this (use the other columns in the columns schema table to generate other things into the ALTER TABLE statements)

select 'alter table [' + table_schema + '].[' + table_name + '] alter column [' + column_name + '] int'
+ CASE WHEN upper(IS_NULLABLE) = 'YES' THEN ' NULL' ELSE ' NOT NULL' END
from information_schema.columns
where data_type = 'tinyint'
and table_name in (select table_name from information_schema.tables where table_type = 'BASE TABLE')

Make sure you backup before you start running these scripts and test them all!

|||That looks pretty good. I was curious what this would look like with the new 2005 syntax using the DMVs. Here is what I came up with... Please note: This needs strenuous testing and a backup like the previous post suggested!!

USE tempdb
GO
IF EXISTS(SELECT * FROM sys.types where name = 'SSN')
DROP TYPE SSN
GO
CREATE TYPE SSN
FROM varchar(11) NOT NULL ;

GO
IF OBJECT_ID('TestSSN') IS NOT NULL
DROP TABLE TestSSN
GO
CREATE TABLE TestSSN ( MySSn SSN)
GO

-- Show the tables with the UDF fields...
SELECT s.name SchemaName, t.name TableName, sc.name ColumnName
FROM sys.schemas s
JOIN sys.tables t
ON s.schema_id = t.schema_id
JOIN sys.columns sc
ON t.object_id = sc.object_id
JOIN sys.types st
ON sc.user_type_id = st.user_type_id
WHERE st.name = 'SSN'
GO

DECLARE @.ErrMsg varchar(255),
@.ErrNum int,
@.SQL nvarchar(max),
@.TableName sysname,
@.ColumnName sysname,
@.SchemaName sysname

DECLARE @.c CURSOR

-- create a cursor to loop through all the tables
SET @.c = CURSOR FOR
SELECT s.name SchemaName, t.name TableName, sc.name ColumnName
FROM sys.schemas s
JOIN sys.tables t
ON s.schema_id = t.schema_id
JOIN sys.columns sc
ON t.object_id = sc.object_id
JOIN sys.types st
ON sc.user_type_id = st.user_type_id
WHERE st.name = 'SSN'
FOR READ ONLY

OPEN @.c
FETCH @.c INTO @.SchemaName, @.TableName, @.ColumnName
WHILE @.@.FETCH_STATUS = 0
BEGIN -- loop
SET @.SQL = 'ALTER TABLE ' + @.SchemaName + '.' + @.TableName + ' ALTER COLUMN ' + @.ColumnName + ' varchar(32) NOT NULL'
PRINT 'Executing: ' + @.SQL
EXEC sp_executesql @.SQL

SELECT @.ErrNum = @.@.Error--, @.RowCnt = @.@.ROWCOUNT
IF (@.ErrNum <> 0)
BEGIN
RAISERROR('Error updating %s.%s table to have %s a varchar(32). Error Numer: %d', 16, 1, @.SchemaName, @.TableName, @.ColumnName, @.ErrNum)
GOTO ErrorHandler
END

FETCH @.c INTO @.SchemaName, @.TableName, @.ColumnName
END -- loop
CLOSE @.c
DEALLOCATE @.c

GOTO ExitScript

ErrorHandler:
RAISERROR (@.ErrMsg, 16, 127)
GOTO ExitScript

ExitScript:
GO

-- See if we have any tables with that UDF anymore...
SELECT s.name SchemaName, t.name TableName, sc.name ColumnName
FROM sys.schemas s
JOIN sys.tables t
ON s.schema_id = t.schema_id
JOIN sys.columns sc
ON t.object_id = sc.object_id
JOIN sys.types st
ON sc.user_type_id = st.user_type_id
WHERE st.name = 'SSN'