Finding Objects in SQL Server During Development

Finding Objects in SQL Server During Development

by JBrooks 13. March 2010 06:57

I kept writing the same SQL over and over while developing to find the names of stored procs or to see what objects I had recently added.  So I created a quick stored proc that I now have in my tool box.

create proc obj(@name varchar(50)=null, @type char(1)=null)
set nocount on
convert(varchar(10),o.create_date,1) created, 
convert(varchar(10),o.modify_date,1) modified
from sys.objects o
where (@name is null or like @name)
and (@type is null or o.type = @type)
and o.is_ms_shipped = 0
order by o.modify_date desc

So if I just do:
obj ‘get%’, ‘P’

I’ll get

name type created modified
GetProjectsTree P 7/28/2008 2/27/2010
GetProjectOutputVariables P 10/10/2009 2/26/2010
GetXrefRequestIssue P 9/9/2008 2/11/2010
GetProjectOutputVariable P 2/11/2010 2/11/2010
GetRequestsItemsOverlap P 1/6/2010 2/9/2010
GetControl P 2/6/2010 2/6/2010
getSourceIdFromPath P 2/5/2010 2/5/2010
GetRequestedFiles P 10/14/2008 2/2/2010
GetRequestsForBranch P 12/30/2008 1/15/2010
GetRequestWithChildren P 7/28/2008 1/1/2010
GetRequestsItems P 4/28/2009 1/1/2010

and if I call obj with no parameters I will get a list of all my objects with the most recently modified objects first. This works with SQL 2005 and above.