challenge...

How can i programmatically via Tsql change the datatype of all the columns of a table to varchar(1000)?

Like I have a table employee

Employee

(

colA int

colB int

colC varchar

)

If i run the tsql..

it should give me

Employee

(

colA varchar

colB varchar

colC varchar

)

Is the table empty? If not, what is the disposition of the data that is already in the table?

|||

Well you could try using a change script generated by Enterprise Manager, like this one:

Code Snippet

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Employee
(
ColA varchar(1000) NULL,
ColB varchar(1000) NULL,
ColC varchar(1000) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Employee)
EXEC('INSERT INTO dbo.Tmp_Employee (ColA, ColB, ColC)
SELECT CONVERT(varchar(1000), ColA), CONVERT(varchar(1000), ColB), ColC FROM dbo.Employee (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Employee
GO
EXECUTE sp_rename N'dbo.Tmp_Employee', N'Employee', 'OBJECT'
GO
COMMIT

|||

Please don't use the graphical tools to make schema changes. It can generate scripts that are inefficient and unnecessary. You can just use ALTER TABLE to change the column from int to varchar in this case. If you do use the tools then please make sure to review the scripts because there is lot of things that can be simplified or improved. For example, in above case there is no reason to do CREATE TABLE and INSERT. You can do SELECT...INTO - this can perform minimally logged operations & can run magnitudes of time faster.