Listing Specific Objects From the DB @ SQL Server 2005

There are always situations when you need to check/find the specific object like table/stored procedure from the database and usually for enterprize level applications there are hundreds or even thousands of user tables and stored procedures.

Following are some of the queries that will help to list the complete details of some specific
Object whose type you are providing in the WHERE clause. You can filter out the results as you needed just by adding couple of more conditions.

select * from sys.objects where type = 'D' -- DEFAULT_CONSTRAINT
select * from sys.objects where type = 'F' -- FOREIGN_KEY_CONSTRAINT
select * from sys.objects where type = 'FN'-- SQL_SCALAR_FUNCTION
select * from sys.objects where type = 'IT'-- INTERNAL_TABLE
select * from sys.objects where type = 'P' -- SQL_STORED_PROCEDURE
select * from sys.objects where type = 'PK'-- PRIMARY_KEY_CONSTRAINT
select * from sys.objects where type = 'S' -- SYSTEM_TABLE
select * from sys.objects where type = 'SQ'-- SERVICE_QUEUE
select * from sys.objects where type = 'U' -- USER_TABLE
select * from sys.objects where type = 'UQ'-- UNIQUE_CONSTRAINT

The following query will result you with the available types of Objects of the database:

select distinct(type) from sys.objects order by type asc