SET QUOTED_IDENTIFIER OFF
go

use wroxdb
go

--
--   Name:  get_table_xml
--
--   Description: return an xml string representing the table schema for use in OPENXML
--
--
IF EXISTS (SELECT * FROM sysobjects WHERE name = 'get_table_xml')
BEGIN
    DROP PROCEDURE get_table_xml
END
GO
 
PRINT 'get_table_xml'
GO
CREATE PROCEDURE get_table_xml @table varchar(30)
AS
DECLARE @xml varchar(4000)
DECLARE @cols int
DECLARE @loop int

	SET NOCOUNT ON

	SELECT @cols = COUNT(*)
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME = @table

	IF @cols = 0
	BEGIN
		RAISERROR 99999 "No columns found"
		RETURN
	END

	
	PRINT "( xact                         char(1), "

DECLARE tbl CURSOR
FOR 
	SELECT convert(char(30),substring(C.COLUMN_NAME,1,30))
				+ " " + CASE WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN DATA_TYPE 
				  ELSE DATA_TYPE + "(" + convert(varchar,CHARACTER_MAXIMUM_LENGTH) + ")"
				  END COLLATE SQL_Latin1_General_CP1_CS_AS
		FROM INFORMATION_SCHEMA.COLUMNS C
		WHERE C.TABLE_NAME = @table
		ORDER BY ORDINAL_POSITION


	SELECT @loop = 0

	OPEN tbl
 
	WHILE 1=1
	BEGIN
	 
	   FETCH NEXT FROM tbl INTO @xml
	   IF @@FETCH_STATUS <> 0 break

	   SELECT @loop = @loop + 1

		IF @loop = @cols
			SELECT @xml = @xml + ")"
		ELSE
			SELECT @xml = @xml + ","

		PRINT @xml
	 
	END
 
CLOSE tbl
DEALLOCATE tbl

GO

GRANT EXECUTE on get_table_xml TO Public
go

-- test
/*

exec get_table_xml 'orders'


*/

