SQL Server - Return SCHEMA for sysobjects

Question

How to I get the SCHEMA when doing a select on sysobjects?

I am modifing a stored procedure called SearchObjectsForText which returns only the Name but I would also like to include the SCHEMA.

Right now it is doing something similar to this:

SELECT DISTINCT name FROM sysobjects 

I would like to know what tables need to be joined to return the SCHEME for each 'name'.

Answer 1

If you mean SQL Server 2005 or higher, use sys.objects instead of sysobjects:

SELECT  sys.objects.name, sys.schemas.name AS schema_name FROM    sys.objects  INNER JOIN sys.schemas ON sys.objects.schema_id = sys.schemas.schema_id 

2005 introduced schemas. up to 2000, users equaled schemas. The same query for SQL Server 2000:

SELECT  sysusers.name AS OwnerName, sysobjects.name FROM sysobjects INNER JOIN sysusers ON sysobjects.uid = sysusers.uid