http://www.sqlservercentral.com/blogs/sql_awesomesauce/2010/10/08/new-hotness_3A00_-sys.objects-and-sys.schemas/

Printed 2014/12/18 11:34AM

New Hotness: sys.objects and sys.schemas

By Jen McCown, 2010/10/08

Last month we talked about the old and busted syscomments- and the new hotness that is sys.sql_modules – for pulling code out of your database using T-SQL.  I’m finally transitioning over to sql_modules for good, even though I’d been a slow adopter.  This month, though, I’m already in SQLlove with the new hotness: sys.objects.

The old sysobjects is still around, though BOL warns us that it “could be going away at any time, so gather your things and move away slowly” (I’m paraphrasing a touch).  sys.objects is a new-in-2005 catalog view that “holds a row for each user-defined, schema-scoped object” in the database.  Note that DDL triggers aren’t schema-scoped, so you’ll go hunting for them in sys.triggers

So, if you’re looking for some stored procedure with the word “Zanzibar” in the name, you can get it with this query:

SELECT Name
, OBJECT_ID
, SCHEMA_ID
FROM sys.objects
WHERE name LIKE '%Zanzibar%'
AND TYPE = 'P'

This new catalog view shows Microsoft’s then-new love for schemas. The name itself is all schema-d up, and we have the nicely named column schema_id that we can use to pull schema/object info, using a join to sys.schemas:

SELECT S.name SchemaName
, O.name ObjectName
, O.type
FROM sys.objects O
INNER JOIN sys.schemas S
ON O.schema_id = S.schema_id

If you’re not familiar yet with schemas, we have three (THREE!) videos on the subject over at MidnightDBA proper:

Happy days!
Jen McCown
http://www.MidnightDBA.com/Jen

Further reading:


Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.