Brett Kaiser (x002548) Blog

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