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 :)