Getting System Defined Procedure Text in SQL Server


Returns one row per system object that contains an SQL language-defined module. System objects of type FN, IF, P, PC, TF, V have an associated SQL module. 

Getting System Defined Procedure text:

select object_name(m.object_id), * from sys.system_sql_modules m
inner join sys.system_objects t on m.object_id=t.object_id
where type='P'


select object_name(m.object_id) as name, * from sys.system_sql_modules m
inner join sys.system_objects t on m.object_id=t.object_id
where type='P' and name='sp_renamedb'


SQL Text of "sp_renamedb":

create procedure sys.sp_renamedb --- 1996/08/20 13:52   @dbname sysname,    -- old (current) db name   @newname sysname    -- new name we want to call it  as   -- Use sp_rename instead.   declare @objid int    -- object id of the thing to rename   declare @bitdesc varchar(30) -- bit description for the db   declare @curdbid int   -- id of database to be changed   declare @execstring nvarchar (max)     --  If we're in a transaction, disallow this since it might make recovery impossible.   set implicit_transactions off   if @@trancount > 0   begin    raiserror(15002,-1,-1,'sys.sp_renamedb')    return (1)   end     --  Only the SA can do this.   if not (is_srvrolemember('dbcreator') = 1)   begin    raiserror(15247,-1,-1)    return (1)   end     --  Make sure the database exists.   if not exists (select * from master.dbo.sysdatabases where name = @dbname)   begin    raiserror(15010,-1,-1,@dbname)    return (1)   end     --  Make sure that the @newname db doesn't already exist.   if exists (select * from master.dbo.sysdatabases where name = @newname)   begin    raiserror(15032,-1,-1,@newname)    return (1)   end     -- Check to see that the @newname is valid.   declare @returncode int   EXEC @returncode = sys.sp_validname @newname   if @returncode <> 0   begin    raiserror(15224,-1,15,@newname)    return(1)   end     -- Don't allow the names of master, tempdb, and model to be changed.   if @dbname in ('master', 'model', 'tempdb')   begin    raiserror(15227,-1,-1,@dbname)    return (1)   end     select @execstring = 'ALTER DATABASE '    + quotename( @dbname , '[')    + ' MODIFY NAME = '    + quotename( @newname , '[')     EXEC (@execstring)     if @@error <>  0   begin    -- No need to raiserror as the CREATE DATABASE will do so    return(1)   end     return (0) -- sp_renamedb  

Thanks :)
Ebook Download
View all
Learn
View all