By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,678 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

column name change

P: 3
Hi, I'm trying to change the column name - date to Sdate in all the tables in my database. As i have many to change so i tried to search all tables and have it change automatically rather than manually however my query doesn't seem to do the job? requesting assistance from anyone is appreciated thank you!


DECLARE @sSQL AS VarChar(500), -- SQL Statement
@sTableName AS VarChar(100) -- TableName

DECLARE CursorTable CURSOR FOR SELECT [NAME] FROM SYSOBJECTS WHERE XTYPE ='U'


OPEN CursorTable
FETCH NEXT FROM CursorTable INTO @sTableName

WHILE @@FETCH_STATUS = 0
BEGIN
SET @sSQL = 'IF EXISTS(SELECT * FROM SYSOBJECTS OBJ ' +
'INNER JOIN SYSCOLUMNS COL ON OBJ.ID = COL.ID ' +
'WHERE OBJ.XTYPE= ''U'' AND OBJ.NAME = ''' + @sTableName + ''' AND COL.NAME = ''DATE'') ' +
' BEGIN ' +
'ALTER TABLE ' + @sTableName + ' ADD TRANDATE DATETIME' +
'UPDATE ' + @sTableName + ' SET TRANDATE=[DATE]' +
'ALTER TABLE ' + @sTableName + ' DROP COLUMN DATE' +
'PRINT ''' + @sTableName + ' DATE Exist''' +
' END'
EXEC (@sSQL)

FETCH NEXT FROM CursorTable INTO @sTableName
END

CLOSE CursorTable
DEALLOCATE CursorTable

--------------------------------------------------------------------------

SELECT [name] FROM Sysobjects WHERE OBJECTPROPERTY(id, N'IsUserTable') = 1

--create CURSOR to Loop every table
IF (PATINDEX('%date%', [name]) > 0)
Begin
Print [name]
End
Mar 28 '06 #1
Share this Question
Share on Google+
3 Replies


Banfa
Expert Mod 5K+
P: 8,916
Why not just use the command

ALTER TABLE t1 CHANGE date SDate DATETIME;

to change the name of the column in your table rather than creating a new column copying the data and deleting the old column.

Of course this is a MySql extension but you haven't said what database you are using.
Mar 28 '06 #2

P: 3
Thanks. I'm using MS SQL 2000
How do i get that extension converted to SQL 2K?
Not really good at MySql
Mar 29 '06 #3

P: 1
I realize this is new post to an old post, but when you are looking for solutions, it would be nice to see one that works simply.

I believe the simple solution to this problem is the stored procedure called:

sp_rename

to quickly rename a file with script do the following:

sp_rename [ @objname = ] 'object_name' , [ @newname = ] 'new_name'
[ , [ @objtype = ] 'object_type' ]


USE AdventureWorks;
GO
EXEC sp_rename 'Sales.SalesTerritory.TerritoryID', 'TerrID', 'COLUMN';
GO


You can find more detailed info in books on-line. This works with MSSQL 2000 and 2005.

____________________
Keep it simple!
Feb 15 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.