SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Generate Format Files for all tables in a db
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

X002548
Not Just a Number

15586 Posts

Posted - 09/16/2003 :  14:11:08  Show Profile  Reply with Quote
This script generates a format file for every table in a database.

It's set up for fixed width files, not too common in this world...but they can easily be modified.

Also the do not at this time handle text or image columns.

Any hints/advice here would be great.

I'll post it when I figure it out.

happy bcping..


SELECT FORMAT_CARD FROM (
	SELECT   '7.0' AS FORMAT_CARD
		, TABLE_NAME, 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, 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
     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
											ELSE 26
			END)
		+ CHAR(9)+'""'+CHAR(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD
		, c.TABLE_NAME, 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
										        ELSE 26
			END)
		+ char(9)+'"\r\n"'+char(9)+CONVERT(varchar(3),ORDINAL_POSITION)+CHAR(9)+COLUMN_NAME AS FORMAT_CARD
		, c.TABLE_NAME, 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
 





Brett

8-)

SELECT @@POST=NewId()

That's correct! It's an AlphaNumeric!

muhiar
Starting Member

37 Posts

Posted - 03/22/2006 :  07:29:40  Show Profile  Reply with Quote
hi iam new to bcp
this code looks greate
i tried it in the query analyzer
but how can i extract it into text format file??
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000