SQL puzzle

[NevG]

Hi troops

I have a sql procedure that queries the system tables, inserts column info into a temp table and then selects * fro xml auto from that temp table - temp table is then cleared up

I have a function to query system tables to work out what parents a table has according to tableID - this returns a table of tablenames for me to use

I want to combine the two so I can do one call to this first procedure which returns me an xml string of columns for alltables needed..

How can I combine the two effectively - Im thinking maybe cursors perhaps..

Thanks guys and gals

Nev G

 

[zhavic]

May be something like this?

DECLARE @i integer
DECLARE @nCount integer
DECLARE @cXml varchar(4000)
DECLARE @cTable_name varchar(200)

CREATE TABLE #xml_result ( ... ) -- with structure of table that returns your stored procedure ( selects * fro xml auto from ... )


SELECT IDENTITY(1,1) AS [ID], [table_name]
INTO #tables
FROM [your_function_that_returns_table_of_tablenames]

/* count of tables */
SET @nCount = @@ROWCOUNT
SET @i = 0
SET @cXml = ''

/* go throught all tables */
WHILE @i < @nCount
BEGIN
SET @i = @i + 1

SELECT @cTable_name = [table_name]
FROM #tables
WHERE [ID] = @i

INSERT INTO #xml_result
EXECUTE your_sp_that_returns_XML @cTable_name -- table name as parameter

END

or you can do it like this:

SELECT *
INTO #temp_table
FROM sysobjects
INNER JOIN your_function_that_returns_table_of_tablenames AS tables ON tables.table_name = sysobjects.name
INNER JOIN syscolumns ON syscolumns.[ID] = sysobjects.[ID] -- join columns
WHERE OBJECTPROPERTY( sysobjects.ID, N'IsUserTable' ) = 1

-- get the result
SELECT * FROM #temp_table FOR XML AUTO


Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.