Thought I lost this...so I figured I better post it. It will create format cards for all tables in the database.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[isp_GenFormatCards]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[isp_GenFormatCards]
GO
CREATE PROC isp_GenFormatCards
AS
DECLARE FormatCard CURSOR FOR
SELECT FORMAT_CARD, TABLE_NAME, TABLE_SCHEMA FROM (
/*
SELECT '--' + TABLE_NAME AS FORMAT_CARD
, TABLE_NAME, null AS COLUMN_NAME, 0 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
*/
SELECT '7.0' AS FORMAT_CARD
, TABLE_NAME, TABLE_SCHEMA, null AS COLUMN_NAME, 1 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Tables
WHERE TABLE_TYPE = 'BASE TABLE'
UNION ALL
SELECT CONVERT(varchar(5),MAX(ORDINAL_POSITION)) AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 2 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
GROUP BY c.TABLE_NAME, c.TABLE_SCHEMA
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)
+ CONVERT(varchar(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
WHEN DATA_TYPE IN ('text','image') THEN 0
ELSE 26
END)
+ CHAR(9)+'""'+CHAR(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 3 AS SQLGroup, ORDINAL_POSITION AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.table_schema = t.table_schema
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION < (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
UNION ALL
SELECT CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+'SQLCHAR'+CHAR(9)+'0'+CHAR(9)+CONVERT(VARCHAR(5),
CASE WHEN DATA_TYPE IN ('char','varchar','nchar','nvarchar') THEN CHARACTER_MAXIMUM_LENGTH
WHEN DATA_TYPE = 'int' THEN 14
WHEN DATA_TYPE = 'smallint' THEN 7
WHEN DATA_TYPE = 'tinyint' THEN 3
WHEN DATA_TYPE = 'bit' THEN 1
WHEN DATA_TYPE IN ('text','image') THEN 0
ELSE 26
END)
+ char(9)+'"\r\n"'+char(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD
, c.TABLE_NAME, c.TABLE_SCHEMA, null AS COLUMN_NAME, 4 AS SQLGroup, 1 AS RowGrouping
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
ON c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
AND TABLE_TYPE = 'BASE TABLE'
WHERE ORDINAL_POSITION = (SELECT MAX(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.Columns i
WHERE i.TABLE_NAME = c.TABLE_NAME)
)AS XXX
ORDER BY TABLE_NAME, COLUMN_NAME, SQLGroup, RowGrouping
DECLARE @Card varchar(200), @TABLE_NAME sysname, @TABLE_SCHEMA sysname, @cmd varchar(200), @x char(2), @Command_String varchar(8000)
, @TABLE_NAME_OLD sysname, @TABLE_SCHEMA_OLD sysname
SELECT @x = '> ', @TABLE_NAME_OLD = '', @TABLE_SCHEMA_OLD = ''
OPEN FormatCard
FETCH NEXT FROM FormatCard INTO @Card, @TABLE_NAME, @TABLE_SCHEMA
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @x = '>>'
IF @TABLE_SCHEMA+@TABLE_NAME <> @TABLE_SCHEMA_OLD+@TABLE_NAME_OLD
BEGIN
SELECT @TABLE_SCHEMA_OLD = @TABLE_SCHEMA
, @TABLE_NAME_OLD = @TABLE_NAME
, @x = '> '
END
SET @cmd = 'echo ' + @Card + ' '+ @x +' d:\Data\Tax\Format\'+@TABLE_SCHEMA+'_'+@TABLE_NAME+'.fmt'
SET @Command_string = 'EXEC master..xp_cmdshell ''' + @cmd + ''', NO_OUTPUT'
PRINT @Command_String
Exec(@Command_String)
FETCH NEXT FROM FormatCard INTO @Card, @TABLE_NAME, @TABLE_SCHEMA
END
CLOSE FormatCard
DEALLOCATE FormatCard
GO
--master..xp_cmdshell 'dir d:\Data\Tax\Format\*.*'