CODEWITHSUNDEEP
sqlsql-server
IWriteApps
IWriteApps

Reputation: 993

How to alter all columns without specifying column_name?

Is there a way to apply an alter statement to ALL the columns in a table without having to specify the column names? This would only be used in a temp table I need to clean up the duplicate data in a report.

Here's a sample of what I'm wondering if its possible:

select T1.Column1, T1.Column2, T1.Column3, T2.Column1, T2.Column2
into #templateTable
from Table1 T1
join Table2 T2 on T1.Column1 = T2.Column2

alter table #templateTable
alter column * null

All I really need is my #tempTable to allow null values in the columns that it originally gets data from which don't previously allow null values.

Lastly, there are 2 reasons I don't want to go through and edit each column:

  1. There are many columns (~50) being pulled from at least 10 tables.
  2. I don't know which ones allow null and researching which ones do would take me the next two days.

Any help will be appreciated.

Upvotes: 2

Views: 1850

Answers (4)

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

Ugly way, but it works in a SqlServer Management Studio, at least (can probably be used as "strings")

select 'alter table ' + table_name + ' alter column '+ column_name +' ' + data_type + 
(case 
  when character_maximum_length is not null and CHARACTER_MAXIMUM_LENGTH <> -1 then ' (' + CAST(CHARACTER_MAXIMUM_LENGTH as varchar) +')' 
  when CHARACTER_MAXIMUM_LENGTH = -1 then '(MAX)'
  else '' end)  + ' null;'  from tempdb.information_schema.COLUMNS
where TABLE_NAME = '<YOUR TABLE NAME>'
and IS_NULLABLE='NO';

copy result, paste and execute...

Upvotes: 4

sam yi
sam yi

Reputation: 4934

I'm guessing this is a one time run... 

select 'alter table xxx alter column ' + columnname + ...
from information_schema.columns
where ...

Copy and paste the result in a separate window and run.

Upvotes: 0

nikolifish
nikolifish

Reputation: 512

I'm not entirely gathering what you are trying to accomplish with your temp tables, but as far as your first question you can query the sysobjects tables for your table's name and then alter each column in that table by looping through the syscolumns table

So lets say I want to loop through each column in the table master:

declare @i int
declare @colcount int
declare @column varchar(max)
declare @sql nvarchar(max)

set @i = 1
set @colcount = (select MAX(colID) from syscolumns where ID = (select id from sysobjects        
where name = 'master'))

WHILE @i < @colcount
BEGIN

set @column = (select name from syscolumns where ID = (select id from sysobjects where name = 'master') and colid = @i)

set @sql = 'select top 1000 [' + @column + '] from master'

EXECUTE(@sql)

set @i = @i + 1

End

you can change @sql to whatever you need it to be and that should get it

Upvotes: 3

Adam Porad
Adam Porad

Reputation: 14471

Check out this question Create a nullable column using SQL Server SELECT INTO?

Upvotes: 1

Related Questions