Showing posts with label stored proc. Show all posts
Showing posts with label stored proc. Show all posts

Wednesday, December 9, 2009

When were the stored procedures last modified?

USE [SOMEdbNAME]
GO
SELECT so.name, so.modify_date
FROM SYS.OBJECTS SO
WHERE SO.name in
   (SELECT ROUTINE_NAME
   FROM INFORMATION_SCHEMA.ROUTINES isr
   WHERE isr.ROUTINE_CATALOG='SOMEdbNAME')
ORDER BY so.modify_date desc

--complements to Igor Malkiman of Qwest Communications for this code

Monday, June 1, 2009

Creating System Stored Procedures

So what are the steps to creating a systemwide stored procedure in SQL Server?
  • Create them in the Master Database
  • Name must start with "sp_"
  • Mark them as System Objects using
    • for sql 2000 - master.dbo.sp_MS_upd_sysobj_category
    • For 2005 and later - sys.sp_MS_marksystemobject
For a test in 2008:
  1. Create a stored procedure in the system databaseone called sp_test
  2. Register them as system stored procs using sp_MS_marksystemobject.
  3. Then try to use them each from another database without prefixing them with master.dbo.