Return User tables and their objects in particular order 1

[darrellblackhawk]

I'm trying to get a result set of User tables and their objects in a particular order. I can get all the objects with the following select statement, but I'm unsure of how to order the result set the way I want it.
[tt]
use Pubs
Select name,id,xtype,parent_obj from sysobjects
where xtype = 'U' or parent_obj in (select id from sysobjects where xtype = 'U')
[/tt]

I want it ordered as follows
-Level 1 User Table
-Level 2 Primary Key
-Level 3 Foreign Keys
-Level 4 Check Constraints
-Level 5 Triggers
-Level 5 Stored Procedures
-Level 6 User defined functions
... Repeated for each User Table

I can write a script to do it, but I'd rather have it in a single select statement.

Darrell

 

[amrita418]

check this example and modify it to your requirement:

select * from sysobjects
order by case when type = 's' then 1
when type = 'k' then 2
when type = 'p' then 3
when type = 'd' then 4
when type = 'u' then 5
else 6 end

Regards,
--aa

 

[darrellblackhawk]

Thanks for responding, but that doesn't do it.
I probably didn't explain what I wanted correctly - maybe this will clarify.

What I want is in the following example:
(Each table listed with its objects listed right below it, then the next table, etc. etc...)
(I can figure out the views and stand-alone indexes after that)

[tt]
Table[blue]1[/blue] {Table name}
Table[blue]1[/blue]PK {Primary key}
Table[blue]1[/blue]FK(s) {Foreign key(s)}
Table[blue]1[/blue]Chk(s) {Check constraint(s)}
Table[blue]1[/blue]TR(s) {Trigger(s)}
Table[blue]1[/blue]SP(s) {Stored Procedures(s)}
Table[blue]1[/blue]UDF(s) {User defined function(s)}

Table[blue]2[/blue]
Table[blue]2[/blue]PK
Table[blue]2[/blue]FK(s)
Table[blue]2[/blue]Chk(s)
Table[blue]2[/blue]TR(s)
Table[blue]2[/blue]SP(s)
Table[blue]2[/blue]UDF(s)
.
.
.
Table[blue]n[/blue]
Table[blue]n[/blue]PK
Table[blue]n[/blue]FK(s)
Table[blue]n[/blue]Chk(s)
Table[blue]n[/blue]TR(s)
Table[blue]n[/blue]SP(s)
Table[blue]n[/blue]UDF(s)
[/tt]

 

[vongrunt]

You can't sort stored procedures and UDF's that way - they don't belong to any table.

For other xtypes, try this:

select isnull(B.name, A.name) as tableName, A.name as objectName, A.xtype, X.objectType
from sysobjects A
left outer join sysobjects B on A.parent_obj=B.id
inner join 
(	select 1 as pos, 'U' as xtype, 'User table' as objectType union
	select 2, 'PK', 'PRIMARY KEY constraint' union
	select 3, 'F', 'FOREIGN KEY constraint' union
	select 4, 'C', 'CHECK constraint' union
	select 5, 'TR', 'Trigger'
) X on X.xtype = A.xtype
where 'U' in (A.xtype, B.xtype)
order by isnull(B.name, A.name), X.pos

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[darrellblackhawk]

Thanks!

That does it.

Darrell