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
Practical Business Intelligence Solutions using the
Microsoft BI Suite of Tools provided along with Microsoft SQL Server
Showing posts with label stored proc. Show all posts
Showing posts with label stored proc. Show all posts
Wednesday, December 9, 2009
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
- Create a stored procedure in the system databaseone called sp_test
- Register them as system stored procs using sp_MS_marksystemobject.
- Then try to use them each from another database without prefixing them with master.dbo.
Labels:
2005,
2008,
sp_ms_marksystemobject,
sql,
stored proc,
system,
system stored proc
Subscribe to:
Posts (Atom)