高級打字員的技術雲

Declare @tableName varchar(50) /*cursor儲存tableName的變數*/
Declare @columnName varchar(50)/*cursor儲存columnName的變數*/

Declare myCursor Cursor
 for
Select tab.name As tableName,c.name As ColumnName from (
Select Top 1 * from sys.tables 
Where type_desc='USER_TABLE'
Order by create_date DESC /*在所有USER_TABLE中create_date最新，只取得該一筆資料就是剛剛新建的Table*/
/* 如果是已存在的資料表，則Where條件直接指定tab.name='資料表名稱' 就不用再Order by 取最新的一筆資料*/
) tab Inner Join sys.columns c on tab.object_id=c.object_id Inner Join sys.types typ on c.system_type_id=typ.system_type_id And c.user_type_id=typ.user_type_id
Where typ.name in ('text','ntext','varchar','char','nvarchar','nchar')


open myCursor
 
 Fetch Next from myCursor into @tableName,@columnName

 While(@@FETCH_STATUS=0)
  Begin
   
    Declare @s nvarchar(MAX)
    Set @s = 'Update ' + @tableName + ' Set ' + @columnName + ' = LTRIM(RTRIM('+@columnName+'))'  
    Exec sp_executesql @s
    Fetch Next from myCursor into @tableName,@columnName  
  End
close myCursor
deallocate myCursor