Querying SQL Server 2008 and 2000 System Objects Table

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:
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s