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

Column name to be changed - URGENT!!

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 27 '06 #1
Share this question for a faster answer!
Share on Google+

Post your reply

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