Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 PROC: TableSchemaToXML
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Onamuji
Aged Yak Warrior

USA
504 Posts

Posted - 02/22/2002 :  11:31:41  Show Profile  Visit Onamuji's Homepage  Send Onamuji an AOL message  Reply with Quote
Well I've always wanted the entire table schema (columns, keys) in XML so I can use an XSLT document to render some SQL code that will create procedures for me... thought maybe somebody else would find it useful...

IF OBJECT_ID('TableSchemaToXML') IS NOT NULL AND OBJECTPROPERTY(OBJECT_ID('TableSchemaToXML'), N'IsProcedure') = 1
DROP PROCEDURE TableSchemaToXML
GO

CREATE PROCEDURE TableSchemaToXML(@TABLE_NAME NVARCHAR(256)) AS
SET NOCOUNT ON

-- DECLARE @TABLE_NAME NVARCHAR(256)
DECLARE @COLUMN_NAME NVARCHAR(256)
DECLARE @ORDINAL_POSITION VARCHAR(256)
DECLARE @COLUMN_DEFAULT VARCHAR(256)
DECLARE @IS_NULLABLE VARCHAR(256)
DECLARE @DATA_TYPE VARCHAR(256)
DECLARE @CHARACTER_MAXIMUM_LENGTH VARCHAR(256)
DECLARE @NUMERIC_PRECISION VARCHAR(256)
DECLARE @NUMERIC_SCALE VARCHAR(256)
DECLARE @PRIMARY_KEY VARCHAR(256)
DECLARE @FOREIGN_KEY VARCHAR(256)
DECLARE @TAG_TYPE BIT

-- SELECT @TABLE_NAME = TABLE_NAME
-- FROM INFORMATION_SCHEMA.TABLES
-- WHERE TABLE_TYPE = 'BASE TABLE'
-- AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), N'IsUserTable') = 1

PRINT '<table name="' + @TABLE_NAME + '">'

SELECT *
INTO #TABLE_COLUMNS
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TABLE_NAME
--SELECT * FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE TABLE_NAME = 'tworkflow'

WHILE (SELECT COUNT(*) FROM #TABLE_COLUMNS) > 0
BEGIN
SELECT @COLUMN_NAME = COLUMN_NAME,
@ORDINAL_POSITION = CONVERT(VARCHAR, ORDINAL_POSITION),
@COLUMN_DEFAULT =
CASE
WHEN COLUMN_DEFAULT IS NULL THEN ''
ELSE CHAR(10) + CHAR(9) + ' default="' + COLUMN_DEFAULT + '"'
END,
@IS_NULLABLE =
CASE UPPER(IS_NULLABLE)
WHEN 'YES' THEN 'true'
ELSE 'false'
END,
@DATA_TYPE = DATA_TYPE,
@CHARACTER_MAXIMUM_LENGTH =
CASE
WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN ''
ELSE CHAR(10) + CHAR(9) + ' length="' + CONVERT(VARCHAR, CHARACTER_MAXIMUM_LENGTH) + '"'
END,
@NUMERIC_PRECISION =
CASE
WHEN NUMERIC_PRECISION IS NULL THEN ''
ELSE CHAR(10) + CHAR(9) + ' precision="' + CONVERT(VARCHAR, NUMERIC_PRECISION) + '"'
END,
@NUMERIC_SCALE =
CASE
WHEN NUMERIC_SCALE IS NULL THEN ''
WHEN NUMERIC_SCALE > 0 THEN CHAR(10) + CHAR(9) + ' numeric-scale="' + CONVERT(VARCHAR, NUMERIC_SCALE) + '"'
ELSE ''
END
FROM #TABLE_COLUMNS
WHERE ORDINAL_POSITION = (SELECT MIN(ORDINAL_POSITION) FROM #TABLE_COLUMNS)

SELECT *
INTO #COLUMN_CONSTRAINTS
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE TABLE_NAME = @TABLE_NAME
AND COLUMN_NAME = @COLUMN_NAME

-- IF (SELECT COUNT(*) FROM #COLUMN_CONSTRAINTS) > 0
-- SET @TAG_TYPE = 0
-- ELSE
SET @TAG_TYPE = 1

IF EXISTS(SELECT * FROM #COLUMN_CONSTRAINTS WHERE COLUMN_NAME = @COLUMN_NAME AND OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), N'IsPrimaryKey') = 1)
SET @PRIMARY_KEY = CHAR(10) + CHAR(9) + ' primary-key="true"'
ELSE
SET @PRIMARY_KEY = ''

IF EXISTS(SELECT * FROM #COLUMN_CONSTRAINTS WHERE COLUMN_NAME = @COLUMN_NAME AND OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), N'IsForeignKey') = 1)
SELECT @FOREIGN_KEY = CHAR(10) + CHAR(9) + ' foreign-key-table="' + OBJECT_NAME(sysreferences.rkeyid) + '"' + CHAR(10) +
CHAR(9) + ' foreign-key-column="' + INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME + '"'
FROM sysreferences,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE sysreferences.constid = (SELECT OBJECT_ID(CONSTRAINT_NAME) FROM #COLUMN_CONSTRAINTS WHERE COLUMN_NAME = @COLUMN_NAME AND OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), N'IsForeignKey') = 1)
AND INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME = (SELECT CONSTRAINT_NAME FROM #COLUMN_CONSTRAINTS WHERE COLUMN_NAME = @COLUMN_NAME AND OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), N'IsForeignKey') = 1)
ELSE
SET @FOREIGN_KEY = ''

PRINT CHAR(9) + '<column name="' + @COLUMN_NAME + '"' + CHAR(10) +
CHAR(9) + ' position="' + @ORDINAL_POSITION + '"' +
@COLUMN_DEFAULT + CHAR(10) +
CHAR(9) + ' nullable="' + @IS_NULLABLE + '"' + CHAR(10) +
CHAR(9) + ' data-type="' + @DATA_TYPE + '"' +
@CHARACTER_MAXIMUM_LENGTH +
@NUMERIC_PRECISION +
@NUMERIC_SCALE +
@PRIMARY_KEY +
@FOREIGN_KEY +
CASE @TAG_TYPE WHEN 1 THEN '/>' ELSE '>' END

DROP TABLE #COLUMN_CONSTRAINTS

DELETE
FROM #TABLE_COLUMNS
WHERE COLUMN_NAME = @COLUMN_NAME
END

DROP TABLE #TABLE_COLUMNS

PRINT '</table>'
GO

Enjoy!

Edited by - onamuji on 02/22/2002 12:02:42
  Previous Topic Topic Next 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.07 seconds. Powered By: Snitz Forums 2000