Домучала птичку наконец-то
Получился вот такой скрипт, который делает экспорт данных изо всех баз на сервере (кроме всяких tempdb и проч.) и создает format files.
Замечания и критика принимаются с энтузиазмом:
-------------------------------------------------------------------------
IF OBJECT_ID('dbo.up_expAlltables') IS NOT NULL
DROP PROCEDURE dbo.up_expAllTables
GO
CREATE PROCEDURE dbo.up_expAllTables
AS
SET NOCOUNT ON
--Declare variables
DECLARE @strDBName SYSNAME --holds database names
DECLARE @lngDBCount INTEGER --holds database count
DECLARE @lngCounter1 INTEGER --loop counter
DECLARE @strTableName SYSNAME --holds table names
DECLARE @lngTabCount INTEGER --holds table count
DECLARE @lngCounter2 INTEGER --loop counter
DECLARE @strSQL NVARCHAR(4000) --dynamic sql string
DECLARE @myQuery NVARCHAR(1000) --bcp query for format files and export
DECLARE @myServer NVARCHAR(100) --server name
DECLARE @myOutPath NVARCHAR(200) --path for exported data files
DECLARE @myFormatPath NVARCHAR(200) --path for format files
DECLARE @myLogin NVARCHAR(50) --login name
DECLARE @myPassword NVARCHAR(50) --password
SET @myServer = (SELECT @@SERVERNAME)
SET @myLogin = [type your username here in quotes]
SET @myPassword = [type your passowrd here in quotes]
--Create temp tables to hold database and table names
CREATE TABLE #tDBName
(
numID INTEGER IDENTITY(1,1)
,strDBName SYSNAME
)
CREATE TABLE #tTableName
(
numID INTEGER IDENTITY(1,1)
,strTableName SYSNAME
)
--Populate database names
INSERT INTO #tDBName (strDBName)
SELECT name from master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','Northwind','pub','tempdb')
SET @lngDBCount = @@ROWCOUNT --How many databases
SET @lngCounter1 = @lngDBCount --Preserves original count for future use
--Loop through database names
WHILE @lngCounter1 <> 0
BEGIN
--Populate database name variable
SET @strDBName = (SELECT strDBName FROM #tDBName WHERE numID = @lngCounter1)
--Populate table names
SET @strSQL = 'INSERT INTO #tTableName (strTableName)
SELECT name FROM ' + @strDBName + '.dbo.sysobjects WHERE xtype = ''u''' --only use user tables
EXEC sp_executesql @strSQL
SET @lngTabCount = (SELECT @@ROWCOUNT) --how many tables in this database
SET @lngCounter2 = @lngTabCount --Preserve table number for future use
--Header
SELECT @strDBName + ' Database'
--Loop through all tables
WHILE @lngCounter2 <> 0
BEGIN
--Populate table name variable
SET @strTableName = (SELECT strTableName FROM #tTableName WHERE numID = @lngCounter2)
-- Core part with creating format files and export files-----
SET @myOutPath = 'C:\SQL\ExportData\' + @strDBName + '_' + @strTableName + '.txt'
SET @myFormatPath = 'C:\SQL\ExportData\' + @strDBName + '_' + @strTableName + '.fmt'
--creating format file
SET @myQuery = N'execute master.dbo.xp_cmdshell ''bcp "' + LTRIM(RTRIM(@strDBName)) +
'..' + LTRIM(RTRIM(@strTableName)) + '" format "' + LTRIM(RTRIM(@myOutPath)) +
'" -S"' + LTRIM(RTRIM(@myServer)) + '" -U"' + LTRIM(RTRIM(@myLogin)) + '" -P"' +
LTRIM(RTRIM(@myPassword)) + '" -f"' + LTRIM(RTRIM(@myFormatPath)) + '" -c'''
execute (@myQuery)
--exporting data
SET @myQuery = N'execute master.dbo.xp_cmdshell ''bcp "' + LTRIM(RTRIM(@strDBName)) +
'..' + LTRIM(RTRIM(@strTableName)) + '" out "' + LTRIM(RTRIM(@myOutPath)) +
'" -S"' + LTRIM(RTRIM(@myServer)) + '" -U"' + LTRIM(RTRIM(@myLogin)) +
'" -P"' + LTRIM(RTRIM(@myPassword)) + '" -c -E'''
execute (@myQuery)
-- core part ends--
--Move backward through tables
SET @lngCounter2 = @lngCounter2 - 1
END
--Move backward through databases
SET @lngCounter1 = @lngCounter1 - 1
--Clean out table name temp table
TRUNCATE TABLE #tTableName
END
DROP TABLE #tDBName
DROP TABLE #tTableName
GO
--Test
EXEC dbo.up_expAllTables
------------------------------------------------------------------
Тенгиз, а вас я просто не знаю как благодарить. Вы так мне все здорово "разжевали"
Сабина
Хочется перебраться в дом тех же размеров, что наш теперяшний, но поближе к цивилизации, детям на колледж, на те же путешествия... 
