-- Variables to hold the data

 DECLARE @Var1 int
 DECLARE @Var2 datetime

 -- CURSOR

 DECLARE db_cursor CURSOR FOR 
 SELECT  Col1,
Col2
 FROM Table1

 OPEN db_cursor  
 FETCH NEXT FROM db_cursor INTO @Var1 , @Var2

 WHILE @@FETCH_STATUS = 0  
 BEGIN   
  -- DO STUFF HERE

  
  FETCH NEXT FROM db_cursor INTO @Var1 , @Var2 
 END  

 CLOSE db_cursor  
 DEALLOCATE db_cursor

BEGIN TRY

 BEGIN TRANSACTION
 
 -- Do whatever you need to do
 

 COMMIT TRANSACTION

END TRY

BEGIN CATCH

 -- Rollback if in a transaction

 IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION
 
 -- Do whatever error logging you need to do

END CATCH

EXECUTE msdb.dbo.sp_send_dbmail @recipients='to@xxx.com',
@subject = 'Test email',
@body = 'Test email body',
@reply_to='from@yyy.com'

-- This returns 1

select ISNUMERIC('1.1')

-- This returns 1

select ISNUMERIC('12')

-- This returns 0

select ISNUMERIC('aaaa')

USE DATABASENAME
GO
DBCC SHRINKFILE(LOGNAME, 1)
BACKUP LOG DATABASENAME WITH TRUNCATE_ONLY
DBCC SHRINKFILE(LOGNAME, 1)

-- =============================================
-- Script to reset COLLATE to database default
-- =============================================

declare @to_collation nvarchar(255)
select  @to_collation = 'Latin1_General_CI_AI'

declare @dbname nvarchar(255)
set @dbname=db_name()
PRINT('ALTERING DATABASE ' + @dbname + ' TO COLLATION '+ @to_collation)

DECLARE COL_CURSOR CURSOR READ_ONLY FOR
select table_schema, table_name, column_name,
column_default, is_Nullable, Data_type,
character_maximum_length, collation_name
from information_schema.columns
INNER JOIN
(SELECT TABLE_NAME TN FROM
information_schema.tables where TABLE_TYPE='BASE TABLE' ) IT ON (TABLE_NAME=TN)
where (Data_type LIKE  '%char%' OR Data_type LIKE '%text%')
AND collation_name <> @to_collation

DECLARE @table_schema varchar(10), @table_name varchar(100), @column_name varchar(100),
@column_default varchar(100), @is_Nullable varchar(5), @Data_type varchar(100),
@character_maximum_length varchar(10), @columncollation varchar(200)

DECLARE @Execstr VARCHAR(2000)
OPEN COL_CURSOR
FETCH NEXT FROM COL_CURSOR INTO @table_schema, @table_name, @column_name,
@column_default, @is_Nullable, @Data_type,
@character_maximum_length, @columncollation
WHILE (@@fetch_status <> -1)
BEGIN
 IF (@@fetch_status <> -2)
 BEGIN
  SET @Execstr = 'ALTER TABLE ' + @table_schema + '.' + @table_name
    + ' ALTER COLUMN [' + @column_name  + '] ' + @Data_type
    
  IF @Data_type <> 'ntext' and @Data_type <> 'text' BEGIN  
   SET @Execstr = @Execstr + ' ('
    +  case when @character_maximum_length = -1 then 'MAX' else @character_maximum_length end
    + ') '
  END
  
  SET @Execstr = @Execstr + ' COLLATE ' + @to_collation
        + CASE WHEN @is_Nullable='no'  THEN  ' NOT NULL' ELSE ' NULL ' END  
    
  
  EXEC (@Execstr)

  PRINT ('Executing -->' + @Execstr )
  PRINT ('--Orig COLLATION WAS -->' + @columncollation )

 END
 FETCH NEXT FROM COL_CURSOR INTO @table_schema, @table_name, @column_name,
 @column_default, @is_Nullable, @Data_type,
 @character_maximum_length, @columncollation
END
CLOSE COL_CURSOR
DEALLOCATE COL_CURSOR
GO

DECLARE @Table VARCHAR(255)   
CREATE TABLE #Results
(
    [Table Name] varchar(100),
    [Number of Rows] varchar(100),
    [Size Reserved] varchar(50),
    [Size Data] varchar(50),
    [Size Indexes] varchar(50),
    [Size Unused] varchar(50)
)

-- Get all user tables

DECLARE cursorTableList CURSOR
FOR SELECT [name]
 FROM dbo.[sysobjects]
 WHERE  OBJECTPROPERTY(id, N'IsUserTable') = 1
FOR READ ONLY

OPEN cursorTableList
FETCH NEXT FROM cursorTableList INTO @Table

WHILE (@@Fetch_Status >= 0)
BEGIN
    INSERT  #Results EXEC sp_spaceused @Table
    FETCH NEXT FROM cursorTableList INTO @Table
END

CLOSE cursorTableList
DEALLOCATE cursorTableList

-- RETURN RESULTS

SELECT * FROM #Results
DROP TABLE #Results

GO

ALTER SCHEMA DestinationSchema TRANSFER SourceSchema.ObjectToMove