Want a query to change a Column name 1

[essa2000]

Dear Programmers ;
I want to change the Field Name of a column of many table , let say 20 tables . Is there any query which can be used to perform that task . I don't want to use SQL Enterprise Manager.

e.g. I want to change Field Name "Seqeuence" to "SortOrder" in 20 tables.

Thanks in advance.
Regards ,
essa2000

 

[jrdatla]

This changes the column name DocId to LatestDocid in a table called Pursuit. Pls try the same on your table and column names. Hope this works.

BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
COMMIT
BEGIN TRANSACTION
EXECUTE sp_rename 'dbo.Pursuit.DocId', 'Tmp_LatestDocId', 'COLUMN'
GO
EXECUTE sp_rename 'dbo.Pursuit.Tmp_LatestDocId', 'LatestDocId', 'COLUMN'
GO
COMMIT

 

[RaoDonthineni]

I have created a stored proc which takes two input parameters
1. Old Column Name
2. New Column Name
This will renames the old name with new name in all the tables
in the current database

create procedure col_rename
@varOldcol sysname,
@varNewcol sysname
as
begin
declare @varTablename varchar(40),
@strSQL varchar(150)
print @varOldcol
print @varNewcol
declare tab_col_cur cursor
for
select TABLE_NAME from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME = @varOldcol

open tab_col_cur
fetch next from tab_col_cur into @varTablename
while @@fetch_status = 0
begin
set @strSQL= 'exec sp_rename ' + '''' + @varTablename + '.' + @varOldcol + ''''+ ', ' + ''''+@varNewcol +''''+ ' , ' + '''' + 'COLUMN' + ''''
exec (@strSQL)
fetch next from tab_col_cur into @varTablename
end
close tab_col_cur
deallocate tab_col_cur
end

 

[essa2000]

Dear jrdatla & RaoDonthineni ;
Thanks for your good help , it really very important query and stored procedures.

Thanks a lot.

essa2000