CODEWITHSUNDEEP
sql-serversql-server-2008stored-proceduressql-server-2012sql-server-2008-r2
Saqib Ali
Saqib Ali

Reputation: 4400

Convert column type from TinyInt to Int in SQL Server

I have a database with a lot of tables. Hundreds of columns are of type TinyInt. I need to convert them all to Int. Is it possible to do that using a stored procedure?

Upvotes: 5

Views: 6715

Answers (1)

marc_s
marc_s

Reputation: 754268

No easy solution for this.

The simplest approach would be to just have a T-SQL script that you can run that would produce the T-SQL ALTER TABLE .... ALTER COLUMN ... scripts as output (on a per database level), which you could then copy & paste in SQL Server Management Studio.

Something like:

SELECT
    ColName = c.Name,
    SchemaName = s.Name,
    TableName = t.Name,
    AlterCommand = 'ALTER TABLE ' + QUOTENAME(s.Name) + '.' + QUOTENAME(t.Name) + 
                   ' ALTER COLUMN ' + QUOTENAME(c.name) + ' INT' + 
                        CASE WHEN c.is_nullable = 1 THEN ' NULL;' ELSE ' NOT NULL;' END
FROM
    sys.columns c
INNER JOIN
    sys.types ty ON ty.system_type_id = c.system_type_id
INNER JOIN 
    sys.tables t ON t.object_id = c.object_id
INNER JOIN 
    sys.schemas s ON s.schema_id = t.schema_id
WHERE
    ty.name = 'tinyint'

The other option would be to wrap this code into a procedure that would create an ALTER TABLE ... ALTER COLUMN ... SQL statement as a dynamic SQL statement, and then execute each one, iterating over all TINYINT columns. But again: this would be on a per-database level.

Alternatively, if you could create those databases from scratch, you could also script out the CREATE TABLE script for all tables into a text file, replace TINYINT with INT in a text editor, and then run those create scripts again against a new database / server.

Upvotes: 6

Related Questions