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\*.*'