column name change

ymcj

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

Banfa

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

ymcj

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

dbmsql

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