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:
Pingback: Tweets that mention SQL Awesomesauce » Blog Archive » New Hotness: sys.objects and sys.schemas -- Topsy.com
You can also use these that don’t need the join to sys.schemas:
SELECT SCHEMA_NAME(O.schema_id) SchemaName
, O.name ObjectName
, O.type
FROM sys.objects O
SELECT OBJECT_SCHEMA_NAME(O.object_id) SchemaName
, O.name ObjectName
, O.type
FROM sys.objects O