Navigation

	Home Page
	Index
	All Pages

Contributor Links

	Create a new Page
	Administration
	File Management
	Login/Logout
	Your Profile

Other Wiki Sections

Software

List Every Database Object - SQL Server

select 
    sortOrder = case o.type
        when 'C'  then '060'
        when 'D'  then '030'
        when 'F'  then '040'
        when 'FN' then '105'
        when 'IF' then '100'
        when 'K'  then '050'
        when 'P'  then '090'
        when 'S'  then '070'
        when 'TF' then '100'
        when 'TR' then '020'
        when 'U'  then '010'
        when 'V'  then '080'
        else o.type
        end, 
   
    type = case o.type
        when 'C'  then 'check constraint'
        when 'D'  then 'default'
        when 'F'  then 'foreign key'
        when 'FN' then 'scalar function'
        when 'IF' then 'table-valued function'
        when 'K'  then 'primary key'
        when 'P'  then 'stored procedure'
        when 'S'  then 'system table'
        when 'TF' then 'table-valued function'
        when 'TR' then 'trigger'
        when 'U'  then 'user table'
        when 'V'  then 'view'
        else o.type
    end,

	o.name,

    parentType = case coalesce(p.type,'')
		when ''   then ''
        when 'C'  then 'check constraint'
        when 'D'  then 'default'
        when 'F'  then 'foreign key'
        when 'FN' then 'scalar function'
        when 'IF' then 'table-valued function'
        when 'K'  then 'primary key'
        when 'P'  then 'stored procedure'
        when 'S'  then 'system table'
        when 'TF' then 'table-valued function'
        when 'TR' then 'trigger'
        when 'U'  then 'user table'
        when 'V'  then 'view'
        else p.type
    end,

	parent = coalesce(p.name,'')

from 
    sysobjects o
	left join sysobjects p
		on o.parent_obj = p.id
 
where 1=1
    and o.type not in ('S', 'IT','SQ')
	and o.name not like 'dt[_]%'

order by 1,3

See Also¶