The syntax to query the system objects table has changed slightly from SQL Server 2000 to SQL Server 2008, but the useful data stored about your server objects is still very good to have access to. It’s worth noting that both DDL and DML trigger information is not stored in the system objects table, but can be viewed in sys.triggers.
As an example, the syntax to query the system objects table in SQL Server 2008 is:
SELECT * FROM sys.objects ORDER BY modify_date
A similar query in SQL Server 2000 by contrast would go against the sysobjects table. Here is a SQL SErver 2000 query that looks through user created objects for ones starting with the text ‘AddressInfo’;
SELECT * FROM sysobjects WHERE xtype = ‘U’ AND name LIKE ‘AddressInfo%’
So the syntax is slightly different when looking at information in SQL Server 2000 and SQL Server 2008.
A query I find to be useful in SQL Server 2008 is to tie together the sys.objects table to the sys.parameters and sys.types tables in order to find the parameters and the data types/sizes of the parameters for a stored procedure accepts. The syntax for this would be:
SELECT sys.parameters.name, sys.parameters.max_length,sys.types.name FROM sys.parameters INNER JOIN sys.objects ON sys.objects.object_id = sys.parameters.object_id INNER JOIN sys.types ON sys.types.system_type_id = sys.parameters.system_type_id WHERE sys.objects.type_desc = 'SQL_STORED_PROCEDURE' AND sys.objects.name='spShowAddressInfo' Resources:
Justin Cooney 