List Every Database Object - SQL Server

The following T-SQL statement lists every object in the database.
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
List Every Database Object - SQL Server

See Also¶
