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