SQL batch update all user data tables in the database with the field type tinyint as int

-- SQL batch update all user data tables in the database with the field type tinyint as int

-- Key notes:
-- 1. query all xtype = '48' records in the system table syscolumns to obtain fields of the type [tinyint ].
-- 2. If the field has a default index before updating the field type, delete the corresponding index first.
-- 3. The data type of the data table field is "dbtype. Byte", which is read by tinyint in codesmith and needs to be corrected.

Declare @ tablename nvarchar (250)

-- Declare that the cursor mycursor1 is used to read all data tables in the database
Declare mycursor1 cursor for select name from DBO. sysobjects where objectproperty (ID, 'isusertable') = 1
-- Open the cursor
Open mycursor1
-- Retrieve the data value from the cursor and assign it to the data table name variable we just declared.
Fetch next from mycursor1 into @ tablename
-- If the cursor is successfully executed
While (@ fetch_status = 0)
Begin

-- Defines the field name variable to be corrected in the cursor
Declare @ columnname nvarchar (255)
Declare @ columnid int
-- Read all fields of tinyint type in a specified data table through a cursor

-- Declare the cursor mycursor2
Declare mycursor2 cursor for select name, colid from syscolumns where id = object_id (@ tablename) and xtype = '48' order by colid
 
-- Open the cursor
Open mycursor2

-- Retrieve the data from the cursor and assign values to the field name variable we just declared.
Fetch next from mycursor2 into @ columnname, @ columnid
 
-- If the cursor is successfully executed
While (@ fetch_status = 0)
Begin
 
-- 1. If the current field has a default index, delete it first.
If exists (select * From SYS. default_constraints where parent_object_id = object_id (@ tablename) and
Parent_column_id = @ columnid)
Begin
Declare @ constraintname nvarchar (255)
Select @ constraintname = Name from SYS. default_constraints where parent_object_id = object_id (@ tablename) and
Parent_column_id = @ columnid
Exec ('alter table ['+ @ tablename +'] Drop constraint ['+ @ constraintname +'] ')

End

-- 2. Update the current field [tinyint] type to [int] type.
Exec ('alter table ['+ @ tablename +'] alter column ['+ @ columnname +'] int ')

-- Use a cursor to retrieve the next record
Fetch next from mycursor2 into @ columnname, @ columnid
End

-- Close the cursor
Close mycursor2
-- Undo cursor
Deallocate mycursor2

-- Use a cursor to retrieve the next record
Fetch next from mycursor1 into @ tablename
End
-- Close the cursor
Close mycursor1
-- Undo cursor
Deallocate mycursor1