How to find the Custom text in a stored procedures using SQL Server

SELECT OBJECT_NAME(object_id)

    FROM sys.sql_modules

    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1

    AND definition LIKE '%yourText%'

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  

Getting System Defined View 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 View 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='V'


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='V' and name='systypes'


SQL Text of "systypes":

CREATE VIEW sys.systypes AS   SELECT    name,    xtype = system_type_id,    status = convert(tinyint, 1 - is_nullable),    xusertype = convert(smallint, user_type_id),    length = max_length,    xprec = precision,    xscale = scale,    tdefault = default_object_id,    domain = rule_object_id,    uid = convert(smallint, schema_id),    reserved = convert(smallint, 0),    collationid = convert(int, collationproperty(collation_name, 'collationid')),    usertype = convert(smallint, typepropertyex(user_type_id, 'oldusertype')),    variable = sysconv(bit, -- nvarchar, varchar or varbinary     case when system_type_id in (165, 167, 231) then 1 else 0 end),    allownulls = is_nullable,    type = xtypetotds(system_type_id, 0),    printfmt = convert(varchar(255), null),    prec = convert(smallint, -- ntext, image or text     case when system_type_id not in (34, 35, 99) then typepropertyex(user_type_id, 'precision') end),    scale = convert(tinyint, typepropertyex(user_type_id, 'scale')),    collation = collation_name   FROM sys.types 

Formulation of sys.all_objects

sys.objects + sys.system_objects == sys.all_objects

You can verify by its count.

select  count(*) from sys.objects
select  count(*) from sys.all_objects
select  count(*) from sys.system_objects

Finding all The User Defined Functions (UDF) in a Database in SQL Server

This helps to find all The User Defined Functions (UDF) in a Database.

USE AdventureWorks;
GO
SELECT name AS function_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc,type
FROM sys.all_objects
WHERE type_desc LIKE '%FUNCTION%';
GO
 
-- Except System Defined one
USE AdventureWorks;
GO
SELECT name AS function_name
,SCHEMA_NAME(schema_id) AS schema_name
,type_desc,type
FROM sys.objects
WHERE type_desc LIKE '%FUNCTION%';
GO

Configuring Ad Hoc Distributed Queries in SQL SEREVER

exec sp_configure 'show advanced options', 1
RECONFIGURE;
exec sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE;

Output:

Configuration option 'show advanced options' changed from 1 to 1. 
Run the RECONFIGURE statement to install.

Configuration option 'Ad Hoc Distributed Queries' changed from 1 to 1. 
Run the RECONFIGURE statement to install.

Reset Identity column to default in SQL Server

The following line resets the Identity value for the Customer table to 
0 so that the next record added starts at 1.

Syntax:

DBCC CHECKIDENT('Customer', RESEED, 0)