declare 

@NewCollation varchar(255)

,@Stmt nvarchar(4000)

,@DBName sysname

set @NewCollation = 'SQL_Latin1_General_CP1256_CI_AS' -- change this to the collation that you need

set @DBName = DB_NAME()

declare 

@CName varchar(255)

,@TName sysname

,@OName sysname

,@Sql varchar(8000)

,@Size int

,@Status tinyint

,@Colorder int

declare curcolumns cursor read_only forward_only local

for select

QUOTENAME(C.Name)

,T.Name

,QUOTENAME(U.Name) + '.' +QUOTENAME(O.Name)

,C.Prec

,C.isnullable

,C.colorder

from syscolumns C

inner join systypes T on C.xtype=T.xtype

inner join sysobjects O on C.ID=O.ID

inner join sysusers u on O.uid = u.uid

where T.Name in ('varchar', 'char', 'text', 'nchar', 'nvarchar', 'ntext')

and O.xtype in ('U')

and C.collation != @NewCollation

and objectProperty(O.ID, 'ismsshipped')=0

order by 3, 1

open curcolumns

SET XACT_ABORT ON

begin tran

fetch curcolumns into @CName, @TName, @OName, @Size, @Status, @Colorder

while @@FETCH_STATUS =0

begin

set @Sql='ALTER TABLE '+@OName+' ALTER COLUMN '+@CName+' '+@TName+ isnull ('('

+CASE @Size WHEN -1 then 'max' else convert(varchar,@Size) end+')', '') +' COLLATE '+ @NewCollation

+' '+case when @Status=1 then 'NULL' else 'NOT NULL' end

--exec(@Sql) -- change this to print if you need only the script, not the action

PRINT @Sql

fetch curcolumns into @CName, @TName, @OName, @Size, @Status, @Colorder

end

close curcolumns

deallocate curcolumns

commit tran

public int GetOrdinal(string tableName, string name)
 {
     DataView schema = GetSchemaTable().DefaultView;
     schema.RowFilter = String.Format("BaseTableName='{0}' AND ColumnName='{1}'", tableName, name);
     if (schema.Count > 0)
         return Convert.ToInt32(schema[0]["ColumnOrdinal"]);
     else
         throw new IndexOutOfRangeException();
 }

DECLARE @SQLString nvarchar (255),
@ParmDefinition nvarchar (255)

DECLARE @rowsCount int
DECLARE @tablename sysname, @Empty char (1)

DECLARE FindNONEmptyTables CURSOR READ_ONLY

FOR SELECT TABLE_SCHEMA+'.'+TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN FindNONEmptyTables

FETCH NEXT FROM FindNONEmptyTables INTO @tablename
WHILE (@@fetch_status = 0)
BEGIN

SET @SQLString = N'
SELECT @rowsCount= COUNT(*) FROM ' + @tablename +
'; IF EXISTS (SELECT * FROM ' + @tablename + ') set
@Empty = ''N'' ELSE set @Empty = ''Y'''
SET @ParmDefinition = N'@tablename sysname, @Empty char(1) OUTPUT, @rowsCount int OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@tablename = @tablename,
@Empty = @Empty OUTPUT,
@rowsCount = @rowsCount OUTPUT

IF @Empty = 'N'
BEGIN
 PRINT @tablename + ' rows count is :' + CAST (@rowsCount AS NVARCHAR(50))
END
FETCH NEXT FROM FindNONEmptyTables INTO @tablename
END

CLOSE FindNONEmptyTables
DEALLOCATE FindNONEmptyTables
GO

DateTime myDate = DateTime.MinValue; //=> 1/1/0001
SqlDateTime mySqlDate = SqlDateTime.MinValue; //=> 1/1/1753
//Note that SQL Server's smalldatetime min value is 1/1/1900

if you want to shrink database log file just run this codes with your database name:

BACKUP LOG yourdbname WITH TRUNCATE_ONLY
DBCC SHRINKFILE(yourdbname_log, 2)

this code will shrink and reduce your database log file to 2MB

SELECT TOP 1 * FROM TableName ORDER BY NEWID()

  exec sp_dropsubscription   @publication =  'tTestFNames' 
     ,  @article =  'tEmployees' 
     ,  @subscriber =  'RSCOMPUTER'
     ,  @destination_db =  'testrep' 
  

  exec sp_droparticle  @publication =  'tTestFNames'
     ,  @article =  'tEmployees'
  

  alter table tEmployees alter column Forename varchar(100) null
  

  exec sp_addarticle  @publication =  'tTestFNames' 
     ,  @article =  'tEmployees' 
     ,  @source_table =  'tEmployees' 
  

  exec sp_addsubscription  @publication =  'tTestFNames'
     ,  @article =  'tEmployees'
     ,  @subscriber =  'RSCOMPUTER' 
     ,  @destination_db =  'testrep' 
  

  exec sp_repladdcolumn  @source_object =  'tEmployees'
     ,  @column =  'TempForename' 
     ,  @typetext =  'varchar(100) NULL' 
     ,  @publication_to_add =  'tTestFNames' 
  

  update tEmployees set TempForename = Forename
  

  exec sp_repldropcolumn  @source_object =  'tEmployees' 
     ,  @column =  'Forename' 
  

  exec sp_repladdcolumn  @source_object =  'tEmployees'
     ,  @column =  'Forename' 
     ,  @typetext =  'varchar(100) NULL' 
     ,  @publication_to_add =  'tTestFNames' 
  

  update tEmployees set Forename = TempForename
  

  exec sp_repldropcolumn  @source_object =  'tEmployees' 
     ,  @column =  'TempForename'
    

You can find the main article in: http://www.sqlservercentral.com/articles/Replication/alteringacolumnonareplicatedtable/1666/

There are two ways :

1. EXEC sp_dboption 'pubs', 'single user', 'TRUE'
   

2. alter database pubs set SINGLE_USER  WITH ROLLBACK IMMEDIATE

To deactive single user mode :

1. EXEC sp_dboption 'pubs', 'single user', 'FALSE'
   

2. 2- alter database pubs set MULTI_USER 

just run sp_who stored procedure.

CREATE TABLE #TmpWho
(spid INT, ecid INT, status VARCHAR(150), 
loginame VARCHAR(150), hostname VARCHAR(150), 
blk INT, dbname VARCHAR(150), cmd VARCHAR(150))

INSERT INTO #TmpWho
EXEC       sp_who

DECLARE @spid INT     
DECLARE @getspid CURSOR     

SET @getspid = CURSOR FOR     
      SELECT       spid
      FROM      #TmpWho
      WHERE       dbname = 'YOURDBNAME'

OPEN @getspid     

FETCH NEXT FROM @getspid INTO @spid     

WHILE @@FETCH_STATUS = 0 
BEGIN
 KILL @spid --SELECT @spid works fine here
FETCH NEXT FROM @getspid INTO @spid
END
CLOSE @getspid 
DEALLOCATE @getspid 

DROP TABLE #TmpWho

DECLARE @tableCatalog VARCHAR (1024) , 

@tableSchema VARCHAR (1024), @tableName VARCHAR (1024), @tableType VARCHAR (1024)
DECLARE cursorTemp CURSOR FOR
SELECT * FROM INFORMATION_SCHEMA.TABLES
OPEN cursorTemp
FETCH cursorTemp INTO @tableCatalog, @tableSchema, @tableName, @tableType


-- start the main processing loop.
WHILE @@Fetch_Status = 0
BEGIN
    EXEC (' ALTER TABLE '+@tableName+' NOCHECK CONSTRAINT ALL;' +
    ' DELETE FROM '+ @tableName +';'+
    ' ALTER TABLE '+@tableName+' CHECK CONSTRAINT ALL ') 
    FETCH cursorTemp INTO @tableCatalog, @tableSchema, @tableName, @tableType
END

CLOSE cursorTemp
DEALLOCATE cursorTemp