ALTER TABLE STATEMENT with generic values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

ALTER TABLE STATEMENT with generic values

Author

Topic

derekathomson
Starting Member

4 Posts

Posted - 2007-06-29 : 07:37:08

Working on my first SQL Server 2005 script. I need to convert all NVARCHAR columns to VARCHAR columns.

I have created a table with the table name, column name, size of the column and if it can be a NULL value. I am then fetching all the rows from this table and attempting to alter the table column type.

ALTER TABLE @TableName
ALTER COLUMN @ColName VARCHAR(@ColSize)

All variables are VARCHARs.

When I run the script I recieve an error message saying that there is incorrect syntax near the ALTER keyword. How do I get the values returned in the variables to be placed into the SQL statement?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-29 : 07:43:13

You can use variable for this. Use dynamic SQL

DECLARE @sql nvarchar(4000)

SELECT @sql = 	'ALTER TABLE ' + @TableName + 
		' ALTER COLUMN ' + @ColName + ' varchar(' + CONVERT(varchar(10), @ColSize) + ')'

EXEC (@sql)

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-06-29 : 07:50:14

Copy the result and execute them(If you are satisfied with the output)

select 'alter table '+table_name+' alter column '+column_name+' varchar(size)' from information_schema.columns
where table_name='table' and data_type='nvarchar'

Madhivanan

Failing to plan is Planning to fail

derekathomson
Starting Member

4 Posts

Posted - 2007-06-29 : 08:35:12

Thanks, that worked a treat.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources