Identify Objects Type using sys.SysObjects

MS SQL Server stores the information about the objects (tables, views, functions, etc) stored in the database in a table, which is accessible by selecting from the sysobjects view (sql2000) or the sys.objects and sys.sysobjects views (sql2005, sql2008).

Object Type Abbreviation

1. AF = Aggregate function (CLR)
2. C = CHECK constraint
3. D = Default or DEFAULT constraint
4. F = FOREIGN KEY constraint
5. L = Log
6. FN = Scalar function
7. FS = Assembly (CLR) scalar-function
8. FT = Assembly (CLR) table-valued function
9. IF = In-lined table-function
10. IT = Internal table
11. P = Stored procedure
12. PC = Assembly (CLR) stored-procedure
13. PK = PRIMARY KEY constraint (type is K)
14. RF = Replication filter stored procedure
15. S = System table
16. SN = Synonym
17. SQ = Service queue
18. TA = Assembly (CLR) DML trigger
19. TF = Table function
20. TR = SQL DML Trigger
21. TT = Table type
22. U = User table
23. UQ = UNIQUE constraint (type is K)
24. V = View
25. X = Extended stored procedure

You can query the sys.SysObjects to get all the objects created with in the database.

Examples

1.  To Get Tables list.

SELECT *
FROM   sys.sysobjects
WHERE  TYPE = 'u'

2.  To Get Stored Procedures list.  

SELECT *
FROM   sys.sysobjects
WHERE  TYPE = 'p'