MS SQL Server – Querying object info

Today I run into a problem of finding out when was the last time a specific Stored Procedure (SP) has been changed.

Obviously, there are different ways to find out, but in this post I would like to introduce some of the possible solutions by using your lovely MS SQL Server Management studio query window. I hope you will find this info useful.

Introduction

Generally there are two ways to retrieve information about database objects. By using either the Sys or INFORMATION_SCHEMA schemas.

So, let’s start by saying that usually you would be too busy to look into the internal SQL Server system tables/views, but it worth mentioning that this is possible, and for sure you would like to get all this information in a really easy way.

Sys.* objects

Objects in this schema contains everything you need to know about the database, indeed, this gives us a direct view on SQL Server internal structure. Sys schema contains so called “System views”. Be aware that there is a large number of system views, and is out of scope of this post to list them all. Anyway, here listed are a couple of “System views” that you will be most probably looking into:

etc… I am sure you got the idea.

What you would probably like to know is how the SQL Server stores the “type” information about the particular object. Please find out the following list:

• C: Check constraint
• D: Default constraint
• F: Foreign Key constraint
• L: Log
• P: Stored procedure
• PK: Primary Key constraint
• RF: Replication Filter stored procedure
• S: System table
• TR: Trigger
• U: User table
• UQ: Unique constraint
• V: View
• X: Extended stored procedure

Information Schema

I am sure you will find very usefull the usage of INFORMATION_SCHEMA that contains a number of VIEWS to the internal database objects. The INFORMATION_SCHEMA is much more user friendly, but gives less information. Here below is a list of some views that INFORMATION_SCHEMA contains (total of 20 views):

etc… again, you got the idea.

So, let’s finally start doing some queries:

How to retrieve the Create and Modify date for a stored procedure

SELECT name, create_date, modify_date
FROM sys.objects
WHERE type = 'P' -- Stored Procedure
OR type = 'V' -- View

you may obtain the same thing by using the following query:

SELECT SPECIFIC_NAME, CREATED, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES

How to retrieve the list of a Stored Procedure Parameters

SELECT sys.parameters.name, so.name , so.*
FROM sys.parameters
INNER JOIN sys.objects so ON so.object_id = sys.parameters.object_id
WHERE so.name = ''

you may obtain the same thing by using the following query:

SELECT PARAMETER_NAME, SPECIFIC_NAME, Data_Type
FROM INFORMATION_SCHEMA.Parameters
WHERE SPECIFIC_NAME = ''

Conclusion

As you have seen, it is really easy to retrieve information about your database objects. This post was just to give an introduction on the subject, so I leave the tough work of discovering all the beauties of the SQL Server internals to you

About the author:

My name is Zoran Maksimovic. I'm a passionate programmer and interested in everything about Software Development, Object-Oriented Design and Software Architecture. Feel free to contact me or to know more about me in the about section.