瓶水相逢

   1:  -- STEP 1, C:\ 建立目錄 bcp

   2:   

   3:  -- STEP 2, 變更伺服器設定

   4:  EXEC sp_configure 'show advanced options', 1

   5:  GO

   6:  RECONFIGURE

   7:  GO

   8:   

   9:  EXEC sp_configure 'xp_cmdshell', 1

  10:  GO

  11:  RECONFIGURE

  12:  GO

  13:   

  14:  -- 切換資料庫

  15:  USE DBName

  16:  go

  17:   

  18:  -- 宣告必要變數

  19:  DECLARE @cmd varchar(2000)

  20:  DECLARE @tableName varchar(100)

  21:   

  22:  -- 宣告 Cursor

  23:  DECLARE tCursor CURSOR FOR

  24:  SELECT name FROM sysobjects WHERE XTYPE = 'U' ORDER BY NAME

  25:   

  26:  -- 開啟 Curosr

  27:  OPEN tCursor

  28:   

  29:  -- 抓取資料

  30:  FETCH NEXT FROM tCursor INTO @tableName

  31:   

  32:  -- 迴圈

  33:  WHILE @@FETCH_STATUS = 0

  34:  BEGIN 

  35:      SET @cmd = 'bcp ' + db_name() + '.[' + @tableName +  '] out "c:\bcp\' + @tableName + '.txt" -c -T -t "," -r "\n"' 

  36:      EXEC master..xp_cmdshell @cmd

  37:      FETCH NEXT FROM tCursor INTO @tableName

  38:  END 

  39:   

  40:  -- 關閉 Cursor

  41:  CLOSE tCursor 

  42:   

  43:  -- 刪除 Cursor

  44:  DEALLOCATE tCursor