--1. 创建如下的存储过程, 实现根据某个fmt文件创建指定名称的表
CREATE PROC dbo.p_fmt2table
@tbname sysname,
@fmtfile nvarchar(1000)
AS
--SELECT @tbname = 'tb',
-- @fmtfile = 'c:\test.fmt'
CREATE TABLE #(col nvarchar(4000))
EXEC('BULK INSERT # FROM ''' + @fmtfile + '''')
DECLARE @rows varchar(10)
SET ROWCOUNT 2
SELECT @rows = col FROM #
DELETE FROM #
DECLARE @i int
SET @i = @rows
SET ROWCOUNT @i
DECLARE @s nvarchar(4000)
SET @s = ''
SELECT @s = @s + ',
' + QUOTENAME(ColName)
+ ' ' + xtype
+ CASE
WHEN xtype IN ('binary','char','nchar','nvarchar','varbinary','varchar','decimal','numeric')
THEN QUOTENAME(datalen, '()')
ELSE '' END
+ CASE
WHEN ColCollate = '' OR ColCollate = '""' THEN ''
ELSE ' COLLATE ' + ColCollate END
FROM(
SELECT id, xtype, plen, datalen, endchar, colid,
ColName = RTRIM(LEFT(col, CHARINDEX(' ', col))),
ColCollate = LTRIM(STUFF(col, 1, CHARINDEX(' ', col), ''))
FROM(
SELECT id, xtype, plen, datalen, endchar,
colid = LEFT(col, CHARINDEX(' ', col)),
col = LTRIM(STUFF(col, 1, CHARINDEX(' ', col), ''))
FROM(
SELECT id, xtype, plen, datalen,
endchar = LEFT(col, CHARINDEX(' ', col)),
col = LTRIM(STUFF(col, 1, CHARINDEX(' ', col), ''))
FROM(
SELECT id, xtype, plen,
datalen = LEFT(col, CHARINDEX(' ', col)),
col = LTRIM(STUFF(col, 1, CHARINDEX(' ', col), ''))
FROM(
SELECT id, xtype,
plen = LEFT(col, CHARINDEX(' ', col)),
col = LTRIM(STUFF(col, 1, CHARINDEX(' ', col), ''))
FROM(
SELECT id,
xtype = STUFF(LEFT(col, CHARINDEX(' ', col)), 1, 3, ''),
col = LTRIM(STUFF(col, 1, CHARINDEX(' ', col), ''))
FROM(
SELECT id = LEFT(col, CHARINDEX(' ', col)),
col = LTRIM(STUFF(col, 1, CHARINDEX(' ', col), ''))
FROM(
SELECT col = REPLACE(col, ' ',' ') + ' ' FROM #
)A
)AA
)AAA
)AAAA
)AAAAA
)AAAAAA
)AAAAAAA
)AAAAAAAA
SET ROWCOUNT 0
IF OBJECT_ID(@tbname) IS NOT NULL
EXEC('DROP TABLE ' + @tbname )
SET @s = 'CREATE TABLE ' + @tbname + '(' + STUFF(@s, 1, 1, '') + ')'
PRINT @s
EXEC(@s)
DROP TABLE #
GO
