Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

New Hotness: sys.objects and sys.schemas

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:

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.