Generating Scripts at the database level

Hi All,

I require to compare two database structures.This is what I went for:

I generated scripts (right click on the database --> All Tasks--> Generate Scripts) for all tables in the two given databases and compared them.

The problem is that the order of the tables in the generated scripts are different. Can I control this order by any possible means?

Thanks in advance.

I do not know of a way offhand via the generate scripts wizard, however you can manually generate a script for each table, copy them to the clipboard, and then paste them into the desired order into a new query window.

hope this helps,

derek

|||

You can 'cut and paste' the script files using your editor (Visual Notepad, for example).

Otherwise, you cannot 'control' the order of elements in the script that the scripting wizard creates.

Generating Multi Level nodes in Stored Procedures

Hi all,

What I am trying to do is generate a stored procedure that is desired
to output XML in this type of format

<Parent Device>
<Device>
<Device ID>1</DeviceID>
<ChildRegister>
<ChildRegisterID>22</ChildRegisterID>
</ChildRegister>
</Device>
<Device>
<Device ID>2</DeviceID>
<ChildRegister>
<ChildRegisterID>23</ChildRegisterID>
</ChildRegister>
</Device>
</Parent Device
The area of concern is the child register, the XML being generated
disregards the Device the ChildRegister belongs to and always places it
as elements of the last device.

<Parent Device>
<Device>
<Device ID>1</DeviceID>
</Device>
<Device>
<Device ID>2</DeviceID>
<ChildRegister>
<ChildRegisterID>23</ChildRegisterID>
</ChildRegister>
<ChildRegister>
<ChildRegisterID>22</ChildRegisterID>
</ChildRegister>
</Device>
</Parent Device
I am trying to produce XML like the first one I described and have yet
to discover a way of associating the ChildRegister with the parent
Device in XML. I am not sure if it is a limitation of SQL Server, or if
my implementation is incorrect. If anyone could post hints or
solutions, I would greatly appreciate it.
A shortened version of the stored procedure is below

Cheers :)
Alvin

SELECT
1AS TAG
,NULL AS PARENT
,NULL AS [Device!2!DeviceID!element]
,NULL AS [ChildRegister!3!RegisterID!element]

FROM udetails INNER JOIN
Detail ON udetails.ID = Detail.ID
WHERE (uDetails.JobID = @.ID)

UNION ALL

SELECT
2 AS TAG
,1 AS PARENT
,TempTable.DeviceIDAS [Device!2!DeviceID!element]
,NULL AS [ChildRegister!3!RegisterID!element]

>From #Temp as TempTable INNER JOIN
device ON TempTable.DeviceID = device.DeviceID

UNION ALL

SELECT
3 AS TAG
,2 AS PARENT
,NULL AS [Device!2!DeviceID!element]
,RegisterID AS [ChildRegister!3!RegisterID!element]

FROM #Temp t INNER JOIN
register ON t.DeviceID =
register.DeviceID

FOR XML EXPLICIT(teohster@.gmail.com) writes:
> I am trying to produce XML like the first one I described and have yet
> to discover a way of associating the ChildRegister with the parent
> Device in XML. I am not sure if it is a limitation of SQL Server, or if
> my implementation is incorrect. If anyone could post hints or
> solutions, I would greatly appreciate it.
> A shortened version of the stored procedure is below

For all problems like this, it is a good idea to post:

o CREATE TABLE statements of the tables inolved.
o INSERT statements with sample data.
o The desired output given the sample data.

You posted the last, but not the first two.

This permits people to post a tested solution to your query. In this
case, an aggrevating factor is that I am not extremely versed in XML,
so I would have to play around with the query.

It may be more effective to ask the real pros in
microsoft.public.sqlserver.xml though.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp